Rename Worksheets to Sequential Dates

[nchristus]

I want to create a workbook with 366 worksheets each named for a day o
the year (1-Jan,2-Jan,3-Jan...).

Is there VBA code that will create worksheets and rename them usin
days of the year?

Thanks

 

[Frank Kabel]

Hi
though not tested I would assume (depending on the data on your
spreadsheets) you won't be able to create 366 separate sheets

 

[Bob Flanagan]

You could do it this way:

Sub CreateSheets()
Dim I As Integer
For I = 1 To 365
Sheets.Add AFTER:=Sheets(Sheets.Count)
ActiveSheet.Name = Format(I, "d-Mmm")
Next
End Sub

Robert Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Doug Glancy]

This assumes that you have just one sheet in your workbook before you start
and you want it to be the "1-Jan" sheet:

Sub test()

Dim I As Long
Dim sheet_date As Date

Application.ScreenUpdating = False
'On Error GoTo err_handler

sheet_date = #1/1/2004#
ThisWorkbook.Worksheets(1).Name = Day(sheet_date) & "-" &
Format((sheet_date), "mmm")

For I = 1 To 365
sheet_date = sheet_date + 1
ThisWorkbook.Worksheets.Add ,
AFTER:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
ActiveSheet.Name = Day(sheet_date) & "-" & Format((sheet_date), "mmm")
Next I

err_handler:
Application.ScreenUpdating = True

End Sub

hth,

Doug Glancy