worksheet change?

[jack]

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

 

[Nigel]

Put this in the 13th Sheet code, it will change ALL other worksheets in the
workbook. NOTE: if you have duplicate names the code will fail!

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim wS as worksheet
For each wS in Worksheets
ws.Name = ws.Range("G1")
Next
End Sub

 

[jack]

Thanks Nigel
I get an error message for line:
ws.Name = ws.Range("G1")
I don't have duplicate names.
Any ideas why it isn't working?
Jack


Put this in the 13th Sheet code, it will change ALL other worksheets in the
workbook. NOTE: if you have duplicate names the code will fail!

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim wS as worksheet
For each wS in Worksheets
ws.Name = ws.Range("G1")
Next
End Sub

 

[Don Guillett]

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

 

[Dave Peterson]

There are some strings that aren't valid.

Strings that include /, \, [, ], *, ? (and others??).

Strings that are too long (max length for a worksheet name is 31 characters).

And at least one reserved name that excel uses: History

I'm guessing that you have a date (which may include slashes depending on your
regional settings) that's causing the trouble.

If you've formatted the cell nicely (using dashes instead of slashes), you could
try:

ws.Name = ws.Range("G1").Text

 

[jack]

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

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
(e-mail address removed)

 

[Rick Rothstein]

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.

 

[Don Guillett]

It may be easier to just send your wb to my addy below with details and
before/after examples

 

[jack]

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

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.

 

[Don Guillett]

How about this to add to all sheets with a number as the last four in the
name

Sub changesheetname()
For i = 2 To Sheets.Count
With Sheets(i)
'If .Name <> "Sheet1" Then
If IsNumeric(Right(.Name, 4)) Then
..Name = Left(.Name, Len(.Name) - 4) _
& Right(.Name, 4) + 1
End If
End With
Next i
End Sub

 

[Rick Rothstein]

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.