Rename sheets

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

"cottage6" wrote:

> 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

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?

"K Dales" wrote:

> Dim ThisSheet as Worksheet
>
> For Each ThisSheet in ThisWorkbook.Worksheets
> ThisSheet.Name = Replace(ThisSheet.Name, "2004", "2005")
> Next ThisSheet
>
> "cottage6" wrote:
>
> > 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

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.

On Thu, 3 Feb 2005 06:01:02 -0800, "cottage6"
<[email protected]> wrote:

>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?
>
>"K Dales" wrote:
>
>> Dim ThisSheet as Worksheet
>>
>> For Each ThisSheet in ThisWorkbook.Worksheets
>> ThisSheet.Name = Replace(ThisSheet.Name, "2004", "2005")
>> Next ThisSheet
>>
>> "cottage6" wrote:
>>
>> > 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

I see a missing parenthesis. Should be:

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

On Thu, 03 Feb 2005 17:05:57 -0600, Myrna Larson
<[email protected]> wrote:

>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.
>
>On Thu, 3 Feb 2005 06:01:02 -0800, "cottage6"
><[email protected]> wrote:
>
>>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?
>>
>>"K Dales" wrote:
>>
>>> Dim ThisSheet as Worksheet
>>>
>>> For Each ThisSheet in ThisWorkbook.Worksheets
>>> ThisSheet.Name = Replace(ThisSheet.Name, "2004", "2005")
>>> Next ThisSheet
>>>
>>> "cottage6" wrote:
>>>
>>> > 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