Worksheet name

[Guest]

Is there a method of using a formula/macro to name a worksheet and have that
worksheet name change according to the month?

i have a workbook with 24 worksheets that are named as Nov 2007 and so on.

Using the computers time and date settings to change a worksheet name once
that month has expired ie on Dec 1 2007, change the worksheet Nov 2007 would
change to Dec 2009, therby keeping 24 worksheets with the next 24 months as
sheetnames.

thanks

 

[Don Guillett]

try this
Sub namesheets()
For i = Sheets.Count To 1 Step -1 'or 24 to 1 step -1
Sheets(i).Name = Format(DateSerial(Year(Date), i, 1), "mmm yy")
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
"curiosity_killed_the_cat"

 

[Guest]

Thankks Don

Could you explain a little on how it works. I pasted your code into vba but
nothing happens. Do i need to put sheet names into the code?

thanks

 

[Don Guillett]

I just re-tested successfully in xl2003.
Did you have any word wrap? The sheets line is one line
1 line for
1 line sheets
1 line next

How did you fire the macro? One way is while cursor in the macro>touch f5
key.

 

[Guest]

Thanks for your effort Don.

I got it working a treat, as it turned out I wasn't describing what I wanted
too good.

Thanks

 

[Don Guillett]

What did you want and what did you use?