Naming worksheets...

[Guest]

I open a workbook each month with a sheet for each day of the month. Is there
an easy way to name the sheets such as 4.02.05,4.03.05,4.04.05 etc?

Thanks,
Tom

 

[Chip Pearson]

Tom,

You'd need to use VBA code to automate this.


Sub RenameSheets()
Dim N As Long
For N = 1 To 31
If N > Worksheets.Count Then
Exit For
End If
Worksheets(N).Name = "4." & Format(N, "00") & ".05"
Next N

End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[JulieD]

Hi Tom

here's a macro that insert new sheets for each day of the current month and
name them as you specified in your post:

Sub createsheets()
For i = 1 To Day(DateSerial(Year(Date), Month(Date) + 1, 0))
Worksheets.Add Before:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Format(Now, "m") & "." & Format(i, "00") &
"." & Format((Now), "yy")
Next
End Sub

however, if you want to copy an existing sheet in the workbook to each new
sheet then the code would be

Sub createsheets()
For i = 1 To Day(DateSerial(Year(Date), Month(Date) + 1, 0))
Sheets("Sheet1").Copy Before:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Format(Now, "m") & "." & Format(i, "00") &
"." & Format((Now), "yy")
Next
End Sub

this code will copy whatever is on the current sheet1 to all of the new
sheets - still naming the way you want.

if you need help implementing the code, please post back.
--

Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway

 

[Guest]

Worked perfectly - Thank You!!

Tom,

You'd need to use VBA code to automate this.


Sub RenameSheets()
Dim N As Long
For N = 1 To 31
If N > Worksheets.Count Then
Exit For
End If
Worksheets(N).Name = "4." & Format(N, "00") & ".05"
Next N

End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com