AutoName Sheets by current date

[Cromag_762003]

Hi everyone...

I was wondering what code I need to use to force excel to rename newly
created worksheets as the current date. ie.(today is 25-May-06,
therefore when I create a new sheet, I want it to name the sheet
25-May-06.)

I know it's a bit silly because I can just name them each day....
however I have one of these for each day of the year and 7 different
workbooks all with the same "naming" scheme.

Any help would be greatly appreciated.

 

[Guest]

hi,
try working something like this in your code.
Sub NameNewSheet()
Dim da As Long
Dim mo As String
Dim yr As Long
da = DatePart("d", Date, dd)
mo = DatePart("m", Date, mm)
yr = DatePart("yyyy", Date, yy)

Sheets.Add
Sheets("Sheet2").Select
Sheets("Sheet2").Name = da & "_" & mo & "_" & yr
End Sub

regards
FSt1

 

[Cromag_762003]

Thanks for your response FSt1...

The code you gave me will work.... however it displays in the format of
"dd_mm_yyyy" is there a way to make it display in the format of
"dd_mmm_yy"

Thanks again for your help

 

[Dave Peterson]

Sub NameNewSheet()
Sheets.Add
Activesheet.name = format(date, "dd_mmm_yy")
End Sub

(But I'd use a 4 digit year--just to avoid confusion/ambiguity.)

 

[Cromag_762003]

Thanks everyone it works great.