edit names of multiple sheet tabs at the same time

  • Thread starter Thread starter J C C
  • Start date Start date
J

J C C

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
 
Well, I see my future spreading out before me. Thanks for the tip, I'll come
back once I've tried this.

John
 
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
 
Back
Top