Update all tabs, incrementing one week per tab

[Ken Peterson]

Hi,

We use Excel 2007 for our weekly time sheet. Each tab is labeled that date
of the Saturday for each week. I have a formula to include the tab name in
the worksheet for that week. Is there a macro or vb code I can use to update
all of the tabs to increment one week for the 52 weeks in a year.

For example, for our 2009 timesheet, the label for tab one (week one) will
be 01-03-2009, for tab 2 (week 2) it will be 01-10-2009, tab 3 (week 3) will
be 01-17-2009, etc.

I am assuming that if I name the first tab 01-03-2009 I should be able to
add 7 days to each tab from the rpevious week's tab, but I do not know how to
do this.

I see tips for incrementing monthly, but not weekly using the date.

Thank you for your help.

 

[Ken Peterson]

I found the answer (with a little modification) in the Excel General
Questions forum.

Full credit to Bob Phillips
Post name: Subject: I want to change the name of (52) worksheets.

Here it is:

Sub mymacro()
Dim i As Long
Dim start As Date
With ActiveWorkbook
start = DateValue(Replace(.Worksheets(1).Name, "", ""))
For i = 2 To .Worksheets.Count
Worksheets(i).Name = Format(start + (i - 1) * 7, "mm-dd-yyyy")
Next i
End With
End Sub