changing dates in worksheets - help

[Guest]

I have 10 different excel spreadsheets, each with 52 worksheets with a date.
It is our new year and the dates were manually entered. my question....

is there a formula i can enter so it picks up the worksheet before it and
adds 7 days to the new worksheets so i do not have to manually change all of
these dates on all 52 worksheets.

right now i have been clicking on each worksheet and manually chnaging the
dates, but getting to be a pain.

Thank you!

 

[Guest]

Paste this code into a standard module. Note Comments through code before
running. Write back if problems.

Sub CreateWeeklySheets()
'This macro assumes you have a New Workbook with only one Worksheet
'with the Name 07/02/2007 (First Monday) of the new year.
Dim TempName As String
Dim i As Integer
Dim c As Integer ' Number of sheets to add
c = 51 'additional weeks to create beyond first sheet
Application.ScreenUpdating = False
For i = 1 To c
'Set Nws = Worksheets.Add After:=Worksheets(i)
TempName = DateValue(ActiveSheet.Name) + 7
Worksheets.Add After:=Worksheets(i)
ActiveSheet.Name = Format(TempName, "mm-dd-yyyy")
Next i
Application.ScreenUpdating = True
End Sub

HTH,

Jim May