edit names of multiple sheet tabs at the same time

I have 59 sheets, with the sheet tab name containing the total number of
sheets: i.e., "Page 5 of 59". After revisions, I now have 63 sheets.

How do I edit all tabs at the same time, such that "Page 5 of 63" appears?
That is, I only want to edit the total number of sheets, all else remains
unchanged.

I searched Help, (with the question: edit names of multiple sheet tabs) it
only suggested editing tabs one at a time.

Any help would be a big timesaver, and greatly appreciated.

Best regards,

John Cole

One way:

Public Sub RenameSheets()
Dim wsSheet As Worksheet
For Each wsSheet In Worksheets
With wsSheet
If .Name Like "*59" Then _
.Name = Left(.Name, Len(.Name) - 2) & "63"
End With
Next wsSheet
End Sub

If you're not familiar with macros, see David McRitchie's "Gettings
Started with Macros" at

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In article ,
"J C C" wrote:

I have 59 sheets, with the sheet tab name containing the total number of
sheets: i.e., "Page 5 of 59". After revisions, I now have 63 sheets.

How do I edit all tabs at the same time, such that "Page 5 of 63" appears?
That is, I only want to edit the total number of sheets, all else remains
unchanged.

I searched Help, (with the question: edit names of multiple sheet tabs) it
only suggested editing tabs one at a time.

Any help would be a big timesaver, and greatly appreciated.

Well, I see my future spreading out before me. Thanks for the tip, I'll come
back once I've tried this.

John

"JE McGimpsey" wrote in message
...
One way:

Public Sub RenameSheets()
Dim wsSheet As Worksheet
For Each wsSheet In Worksheets
With wsSheet
If .Name Like "*59" Then _
.Name = Left(.Name, Len(.Name) - 2) & "63"
End With
Next wsSheet
End Sub

If you're not familiar with macros, see David McRitchie's "Gettings
Started with Macros" at

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In article ,
"J C C" wrote:

I have 59 sheets, with the sheet tab name containing the total number of
sheets: i.e., "Page 5 of 59". After revisions, I now have 63 sheets.

How do I edit all tabs at the same time, such that "Page 5 of 63"
appears?
That is, I only want to edit the total number of sheets, all else
remains
unchanged.

I searched Help, (with the question: edit names of multiple sheet tabs)
it
only suggested editing tabs one at a time.

Any help would be a big timesaver, and greatly appreciated.

Hooooo boy, you da man. Tried it, and the first time, it worked! Now that's
unusual (for me). I'm off and running into the wonderful world of VBA. My
life is saved, once again, by a newsgroup.
Many thanks,

John

"J C C" wrote in message
...
Well, I see my future spreading out before me. Thanks for the tip, I'll
come
back once I've tried this.

John

"JE McGimpsey" wrote in message
...
One way:

Public Sub RenameSheets()
Dim wsSheet As Worksheet
For Each wsSheet In Worksheets
With wsSheet
If .Name Like "*59" Then _
.Name = Left(.Name, Len(.Name) - 2) & "63"
End With
Next wsSheet
End Sub

If you're not familiar with macros, see David McRitchie's "Gettings
Started with Macros" at

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In article ,
"J C C" wrote:

I have 59 sheets, with the sheet tab name containing the total number
of
sheets: i.e., "Page 5 of 59". After revisions, I now have 63 sheets.

How do I edit all tabs at the same time, such that "Page 5 of 63"
appears?
That is, I only want to edit the total number of sheets, all else
remains
unchanged.

I searched Help, (with the question: edit names of multiple sheet
tabs)
it
only suggested editing tabs one at a time.

Any help would be a big timesaver, and greatly appreciated.