Naming worksheets...

T

tojo107

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
 
C

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
 
J

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
 
T

tojo107

Worked perfectly - Thank You!!

Chip Pearson said:
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
Click to expand...
 
Top