renaming sheet tab with a date from cell

DerbyDad03 January 29, 2010 at 10:44:38

This code will build the Sheet Name by using the date stored in the cell. BTW...you 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" or Sheets(1).Name = "My Sheet Name" is more efficient. The same goes for cell ranges, text boxes, whatever. Instead of: Range("A1:A100").Select Selection.Interior.ColorIndex = 3 use 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

Worked fine. Thanks for the help and the advice. I'm extremely new to VB.

Humar January 29, 2010 at 15:18:24

Hi, 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 Regards

DerbyDad03 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

Humar 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? Regards

DerbyDad03 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.