Renaming Worksheets

[Steve Walford]

Is there any easy way to rename or name worksheets

I have a 52 page spread sheet, each page represents a week and is
named like so
1st January,8th January,15thJanary etc

The date used represents the first day of the week (Monday)

The next year I copy the spreadsheet and delete all the data, but the
date on the page does no correspond to Monday so I have to rename all
the sheets

Is there an easier way

Thanks

[Gary Brown]

How about WEEK01, WEEK02, WEEK03.... ?
HTH,
Gary Brown


"Steve Walford" wrote:

> Is there any easy way to rename or name worksheets
>
> I have a 52 page spread sheet, each page represents a week and is
> named like so
> 1st January,8th January,15thJanary etc
>
> The date used represents the first day of the week (Monday)
>
> The next year I copy the spreadsheet and delete all the data, but the
> date on the page does no correspond to Monday so I have to rename all
> the sheets
>
> Is there an easier way
>
> Thanks
>
>

[Gary Brown]

That said,
Except for the 'st', 'nd', 'th', try this...
'/=================================================/
Sub Test_Change_WkshtName()
Dim wksht As Worksheet
Dim dtStart As Date, dtWksht As Date

'1st Monday of the Year
dtStart = DateValue("01/04/2005")

dtWksht = dtStart

For Each wksht In Worksheets
wksht.Name = Format(dtWksht, "dd mmmm")
dtWksht = dtWksht + 7
Next wksht

End Sub
'/=================================================/
HTH,
Gary Brown


"Gary Brown" wrote:

> How about WEEK01, WEEK02, WEEK03.... ?
> HTH,
> Gary Brown
>
>
> "Steve Walford" wrote:
>
> > Is there any easy way to rename or name worksheets
> >
> > I have a 52 page spread sheet, each page represents a week and is
> > named like so
> > 1st January,8th January,15thJanary etc
> >
> > The date used represents the first day of the week (Monday)
> >
> > The next year I copy the spreadsheet and delete all the data, but the
> > date on the page does no correspond to Monday so I have to rename all
> > the sheets
> >
> > Is there an easier way
> >
> > Thanks
> >
> >

[Steve Walford]

Garry this did exactly what I wanted

Many Thanks

Steve


On Tue, 29 Mar 2005 12:37:07 -0800, "Gary Brown"
<[email protected]> wrote:

>That said,
> Except for the 'st', 'nd', 'th', try this...
>'/=================================================/
>Sub Test_Change_WkshtName()
> Dim wksht As Worksheet
> Dim dtStart As Date, dtWksht As Date
>
> '1st Monday of the Year
> dtStart = DateValue("01/04/2005")
>
> dtWksht = dtStart
>
> For Each wksht In Worksheets
> wksht.Name = Format(dtWksht, "dd mmmm")
> dtWksht = dtWksht + 7
> Next wksht
>
>End Sub
>'/=================================================/
>HTH,
>Gary Brown
>
>
>"Gary Brown" wrote:
>
>> How about WEEK01, WEEK02, WEEK03.... ?
>> HTH,
>> Gary Brown
>>
>>
>> "Steve Walford" wrote:
>>
>> > Is there any easy way to rename or name worksheets
>> >
>> > I have a 52 page spread sheet, each page represents a week and is
>> > named like so
>> > 1st January,8th January,15thJanary etc
>> >
>> > The date used represents the first day of the week (Monday)
>> >
>> > The next year I copy the spreadsheet and delete all the data, but the
>> > date on the page does no correspond to Monday so I have to rename all
>> > the sheets
>> >
>> > Is there an easier way
>> >
>> > Thanks
>> >
>> >