Rename sheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Dim ThisSheet as Worksheet

For Each ThisSheet in ThisWorkbook.Worksheets
ThisSheet.Name = Replace(ThisSheet.Name, "2004", "2005")
Next ThisSheet
 
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?
 
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.
 
I see a missing parenthesis. Should be:

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