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

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

"curiosity_killed_the_cat"
m wrote in message
...
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

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" wrote:

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

"curiosity_killed_the_cat"
m wrote in message
...
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

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 macrotouch f5
key.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"curiosity_killed_the_cat"
wrote in message ...
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" wrote:

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

"curiosity_killed_the_cat"
m wrote in message
...
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

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" wrote:

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 macrotouch f5
key.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"curiosity_killed_the_cat"
wrote in message ...
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" wrote:

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

"curiosity_killed_the_cat"
m wrote in message
...
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

What did you want and what did you use?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"curiosity_killed_the_cat"
wrote in message ...
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" wrote:

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 macrotouch f5
key.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"curiosity_killed_the_cat"

wrote in message
...
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" wrote:

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

"curiosity_killed_the_cat"
m wrote in message
...
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