CAN EXCEL AUTO DATE ADDED WORKSHEETS?

[Dean W.]

I make multiple worksheets for different days of the month. I end up copying
and then renaming. Is there a way to AUTO-FILL the consecutive dates so I
don't have to rename 28-31 sheets?

 

[Gord Dibben]

Start with one sheet which you will copy.

Sub Add_Sheets_Months()
Call AddMonthSheets(6, 2008) 'Adds June 2008 daily sheets
End Sub

Public Sub AddMonthSheets(ByRef Mnth As Integer, ByRef Yr As Integer)
'Chip Pearson code.
Dim wks As Worksheet
Dim dte As Date
Dim lCounter As Long

Set wks = Sheets("Sheet1") 'Sheet to be copied
wks.Select
For lCounter = 1 To 31
dte = DateSerial(Yr, Mnth, lCounter)
If Month(dte) = Mnth And (Weekday(dte) <> 1 And _
Weekday(dte) <> 7) Then
wks.Copy After:=ActiveSheet
ActiveSheet.Name = Format(DateSerial(Yr, Mnth, lCounter), "Mmm dd ")
End If
Next lCounter

End Sub


Gord Dibben MS Excel MVP