Worksheet tab labels

[Brenda463]

I have a number of worksheets that I use on an annual basis, updated monthly.
It is best if I keep each worksheet and save it indefinitely because it is
finacnce related. Since I have to recreate every year it would be fastest if
I could use the completed one and update the tab labels to reflect the
current year. I use the month and the year on the tab of the worksheet. I
would like to know if there is a way to change the year on each tab to the
new year and then do a save as and rename it to the current year.
I think that it would be much faster if I did not have to go to each
individual sheet and click on the page then right click and select rename and
change the year twelve times per project. I would really appreciate any input
that someone could offer to speed up this process.

 

[Bob Umlas, Excel MVP]

A short routine would do it...
Sub UpdateTabs()
On Error Resume Next
For Each tbb In Sheets
tbb.Name = Application.Substitute(tbb.Name, Year(Date) - 1,
Year(Date))
Next
End Sub

 

[Brenda463]

How do I go about using the routine?

A short routine would do it...
Sub UpdateTabs()
On Error Resume Next
For Each tbb In Sheets
tbb.Name = Application.Substitute(tbb.Name, Year(Date) - 1,
Year(Date))
Next
End Sub

 

[Bob Phillips]

You can just add it and run it
- go into the VBIDE (Alt-F11)
- insert a module (menu Insert>Module)
- paste the code into the module code pane that shows
- select anywhere within the procedure and run it, F5


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Brenda463]

I cannot seem to get this to run. Do I need to save this first. I did put it
in. I set the year(7) - 1, Year(8). and I did the F5 with the worksheet that
I was basing it on open. A macro module opened that reflected this (Update
Tabs) and gave me the option to run it. I clicked on Run. Nothing happened.
Any idea what I might be overlooking?

 

[Gord Dibben]

Don't change the word "Date" to 7 or 8

Year(Date) means return the Year of today's date.

Since today is in 2008, the Year(Date) -1 looks for 2007 in the sheet name.

The next Year(Date) returns 2008 and changes the 2007 to 2008


Gord Dibben MS Excel MVP

 

[Brenda463]

Thank you. I had run it that way originally. My problem was that the year was
in 2 digits only and it could not find it. I corrected that and it works. Do
I need to save this in order to reuse it again in future years? If so, please
tell me what is the best way to do that.

 

[Gord Dibben]

There is no "best" way.

If you have a Personal.xls you would copy the macro and stick it in a module in
that file.

You would have to make changes to the code to point to the activeworkbook

Sub UpdateTabs()
On Error Resume Next
For Each tbb In ActiveWorkbook.Sheets
tbb.Name = Application.Substitute(tbb.Name, _
Year(Date) - 1, Year(Date))
Next
End Sub

Or you could just leave it in the workbook in which it now resides.


Gord