renaming sheet tab with a date from cell

Microsoft Excel 2003 (full product)
January 29, 2010 at 10:18:41
Specs: Windows XP
Does anyone know how to rename the sheet name with a date? Everytime I write the macro to rename the sheet it shows the date with slashes (1/29/10) even though the date in the cell is formatted with dahses (1-29-10).

Using macro like this


See More: renaming sheet tab with a date from cell

Report •

January 29, 2010 at 10:44:38
This code will build the Sheet Name by using the date stored in the cell. don't have to Select the ActiveSheet, it's already "selected" since it's Active.

In addition, you rarely have to Select an object to perform a VBA Action on it.

ActiveSheet.Name = "My Sheet Name"


Sheets(1).Name = "My Sheet Name"

is more efficient.

The same goes for cell ranges, text boxes, whatever.

Instead of:

Selection.Interior.ColorIndex = 3


Range("A1:A100").Interior.ColorIndex = 3

Option Explicit
Sub NameDateSheet()
Dim myShtName
myShtName = Month(Range("A47")) _
            & "-" & Day(Range("A47")) _
            & "-" & Year(Range("A47"))
ActiveSheet.Name = myShtName
End Sub

Report •

January 29, 2010 at 13:13:11
Worked fine. Thanks for the help and the advice. I'm extremely new to VB.

Report •

January 29, 2010 at 15:18:24

Try this
ActiveSheet.Name = Range("A47").Text

By using the text attribute of Range("A47") you get the format used in the cell.

Using just Range("A47") takes the default 'Value' of the cell, which gives you the / / format.

Using just Range("A47") is the same as
ActiveSheet.Name = Range("A47").Value


Report •

Related Solutions

January 29, 2010 at 16:17:57
Did someone change the Sheet Name rules and not tell me?

A sheet name must be 31 or fewer characters and cannot contain the characters [ ] * ? / \ .

A sheet cannot be named History

Report •

January 30, 2010 at 04:35:57
Hi DerbyDad03,

Am I missing something in your response (#4).

Who said anything about changing the Sheet naming rules, ...

In my post the sheet name did not used a reserved word or any of the disallowed characters,

... or were you referring to something else?


Report •

January 30, 2010 at 07:13:32
My error...I thought you were suggesting a way to get the slashes in the sheet name.

Thanks for bringing that to my attention.

Report •

Ask Question