Rename sheets

[cottage6]

I want to save a 2004 file and modify it to use for this year, as I need some
of the data. There are 52 sheets in the current file labeled with 2004
Saturday week ending dates. I have code as follows that created the sheets
and labeled them. Since the sheets will already exist and be named when I
copy the file over, I wondered if there was a way to rename them with 2005
week ending dates. Any help would be greatly appreciated!

Dim i As Long
Dim dte As Date
dte = CDate("1 JAN 2005")
For i = 1 To 51
dte = dte + 7
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "WE " & Format(dte, "mmddyy")
Next
End Sub

 

[K Dales]

Dim ThisSheet as Worksheet

For Each ThisSheet in ThisWorkbook.Worksheets
ThisSheet.Name = Replace(ThisSheet.Name, "2004", "2005")
Next ThisSheet

 

[cottage6]

Thanks for the response. My problem is not only does the year change, but
the Saturday week ending dates will change as well. So I need to rename the
first sheet to 1 Jan 2005, then get the others to rename using that as an
example. Any ideas?

 

[Myrna Larson]

Assuming the sheets are in chronological order:

Dim s As Long
Dim Date1 As Date

Date1 = #1/3/2005#
For S = 1 To ThisWorkbook.Worksheets.Count
Worksheets(S).Name = UCase$(Format$(Date1,"D MMM YYYY")
Date1 = Date1 + 7
Next S

I used a constant for the starting date. Change that as needed.

 

[Myrna Larson]

I see a missing parenthesis. Should be:

Worksheets(S).Name = UCase$(Format$(Date1,"D MMM YYYY"))