Add new sheets with week ending dates

[Guest]

Is there a way I can add 51 new sheets to a workbook that names those sheets
with a week ending date? Sheet 1 is currently names WE 110604; I would like
an additional 51 sheets with each new week ending date. Sheet 2 would be WE
111304, Sheet 3 would be WE 112004, and so on. I appreciate the help, thanks!

 

[Norman Jones]

Hi Cottage6,

Try:

Sub Tester01()
Dim i As Long
Dim dte As Date

dte = CDate("6 Nov 2004")

For i = 1 To 51
dte = dte + 7
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "WE " & Format(dte, "mmddyy")
Next

End Sub

 

[Glen Mettler]

Here is one way:
' Creates sheets in reverse order so sheets are in ascending order when
finished

Sub WeekMake()

StartDate = #11/6/2004#
'Calculate last Week
mydays = 51 * 7
'Establish ending week
EndWeek = StartDate + mydays
'Add 1 more week to last week so for loop works
WeekEnding = EndWeek + 7
For s = 1 To 51
Sheets.Add
WeekEnding = WeekEnding - 7
SheetName = "WE " & Month(WeekEnding) & Day(WeekEnding) &
Right(Year(WeekEnding), 2)
ActiveSheet.Name = SheetName
Next s

End Sub