How do have incremental tab names in excel

October 6, 2010 at 17:27:38
Specs: Windows XP
I'm looking to have a seperate tab for each day of the year i.e. Sat Jan 1 to Sat Dec 31. I'm hoping there is a short cut to do this quickly

See More: How do have incremental tab names in excel

Report •

#1
October 6, 2010 at 20:39:03
Here's 2 versions of code to create 365 sheets, starting with Sat Jan 1 and ending with Sat Dec 31.

The first code shows how each part of the sheet name is extracted from the Date.

The second just does it with no breakdown.

Option Explicit
Sub DailySheets()
Dim startDate, nxtDay, newDate, theDate As Integer
Dim theDay, theMonth As String
'Numerical equivalent of January 1, 2011
  startDate = 40544
'Loop to add and name sheets
   For nxtDay = 0 To 364
'Increment numerical Date variable
    newDate = startDate + nxtDay
'Extract Day Name from Date variable
     theDay = Left(WeekdayName(Weekday(newDate)), 3)
'Extract date from Date variable
     theDate = Day(newDate)
'Extract Month name from Date variable
     theMonth = MonthName(Month(newDate), True)
'Add a new sheet after the last sheet
    Sheets.Add after:=Sheets(Sheets.Count)
'Name the new sheet with the Day, MonthName and Date
      ActiveSheet.Name = theDay & " " & theMonth & " " & theDate
   Next
End Sub

Option Explicit
Sub DailySheets1()
Dim startDate, nxtDay, newDate, theDate As Integer
Dim theDay, theMonth As String
'Numerical equivalent of January 1, 2011
  startDate = 40544
'Loop to add and name sheets
   For nxtDay = 0 To 364
'Increment numerical Date variable
    newDate = startDate + nxtDay
'Add a new sheet after the last sheet
    Sheets.Add after:=Sheets(Sheets.Count)
'Name the new sheet with the Day, MonthName and Date
      ActiveSheet.Name = _
        Left(WeekdayName(Weekday(newDate)), 3) & " " _
      & MonthName(Month(newDate), True) & " " _
      & Day(newDate)
   Next
End Sub


Report •
Related Solutions


Ask Question