I am assuming that somewhere on the yearly calendar worksheet you have a
cell with just the year number in it which, for this example, I'll assume is
cell A1 (located in two locations within my code). Right click the tab on
your yearly calendar worksheet and copy/paste this code into the code window
that opened up...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Dim Parts() As String
Const Months As String = "JanFebMarAprMayJunJulAugSepOctNovDec"
If Target.Address(0, 0) = "A1" Then
For Each WS In Worksheets
Parts = Split(WS.Name, " ")
If UBound(Parts) = 1 Then
If InStr(Months, Left(Parts(0), 3)) 0 _
And Parts(1) Like "####" Then
WS.Name = Replace(WS.Name, Parts(1), Range("A1").Value)
End If
End If
Next
End If
End Sub

Now, go back to the yearly calendar worksheet and change A1 to a different
year number and see if just those 12 worksheets' names change correctly.

--
Rick (MVP - Excel)


"jack" wrote in message
...
The exact text format is January 2009, Feburary 2009, etc. It is the full
month name
followed by a space followed by the year.
That is what I have in the cell "G1" and cell "G1" is linked to the yearly
calendar worksheet.
Jack

"Rick Rothstein" wrote in message
...
When making changes to text... the exact text that is being changed is
kind
of important. When you say "monthly sheet names such as Jan 2009"... is
that
the exact format for the sheet names you want to change (3 letter
abbreviated month name followed by a space followed by the year) or did
you
simplify the name for example purposes? If you simplified it, we need the
real sheet name format that you are using.

--
Rick (MVP - Excel)


"jack" wrote in message
...
A broader explanation on what I am trying to accomplish:
I have a yearly calendar on one worksheet and I decided to setup
printable
monthly calendars on 12 separate worksheets. I've linked the 12 monthly
sheets to the yearly calendar sheet so that when I change the yearly
calendar worksheet, the monthly calendar sheets change according to the
year. I am trying to change the monthly sheet names such as Jan 2009 to
Jan
2010, etc. when changing the yearly calendar sheet. I want to change the
sheet names on the monthly sheets only, not the yearly calendar sheet and
one other sheet names.
It appears that the coding that Nigel offered will change / update all of
the worksheets and my fault that I didn't explain I want to change only
the
12 worksheets names and leave the others as - is.
Any help on how to do this naming / renaming of the 12 worksheet tabs
would
be greatly appreciated. I'm attempting to work / modify the code that
Nigel offered, but I'm not getting the results desired.
Merry Christmas,
Jack

"Don Guillett" wrote in message
...
Please give before/after examples. You can't have 2 worksheet names the
same. What are you trying to do?
Merry Xmas


Don Guillett
Microsoft MVP Excel
SalesAid Software

"jack" wrote in message
...
I have the following brief macro to change the tab name based on a change
to
cell "G1":

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
ActiveSheet.Name = ActiveSheet.Range("G1")
End Sub

How do I change the tab name on a total of 12 worksheets (different
names)
when I make a change on the 13th worksheet?
The 12 worksheets all are linked to the 13 worksheet so that when I
change
the 13th sheet, the others are updated and I would like to update
(change)
the tab names when the the sheets are updated.
I've thought about placing the above macro in each worksheet but I can't
even get the one to update (name change) when I modify the 13th sheet.
Any guidance would be greatly appreciated.
Jack