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
ActiveSheet().Select
ActiveSheet.Name=Range("A47")
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 = 3use
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.
Hi, Try this
ActiveSheet.Name = Range("A47").TextBy 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").ValueRegards
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
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
My error...I thought you were suggesting a way to get the slashes in the sheet name. Thanks for bringing that to my attention.
Yes (14) | ![]() | |
No (14) | ![]() | |
I don't know (15) | ![]() |