We help IT Professionals succeed at work.

Getting a date as a sheet tab name using VBA?

RWayneH
RWayneH asked
on
Had a date in a cell, stored it to clipboard to use as a sheet tab name.  How come I can do this manually, but not when it is run as a macro?  Please advise and thanks

Trying:

Sheets("Historical_SelectionStocking_Ef").Select
    Range("E1").Select
    Selection.NumberFormat = "mm-dd-yy"
    NewSheetTabName = ActiveCell.Value
    Sheets("Historical_SelectionStocking_Ef").Name = NewSheetTabName
Comment
Watch Question

View Solution Only

Sam JacobsCitrix Technology Professional / Director of TechDev Services, IPM

Commented:
Try this:
    Sheets("Historical_SelectionStocking_Ef").Select
    NewSheetTabName = Format(Range("E1").Value, "mm-dd-yy")
    Sheets("Historical_SelectionStocking_Ef").Name = NewSheetTabName

Open in new window

You could do it all in one line if you wish:
Sheets("Historical_SelectionStocking_Ef").Name = Format(Sheets("Historical_SelectionStocking_Ef").Range("E1").Value, "mm-dd-yy")

Open in new window

Finance Analyst
Commented:
To add to Sam's comment, I assume you are getting a a tab name with a number in the 40000+ region.

That is the serial number that Excel uses as a date, it represent number of days since Excel's internal day which is 01 Jan 1900; today (3 Feb 2020) is serial number 43864.

Your original script was formatting the cell to show as a recognisable date but then the NewSheetTabName was taking the raw unformatted value to use as the variable.

Sam's suggestion is taking the raw value and formatting it for the variable.
RWayneH

Author

Commented:
Thanks for the help.