If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
edit names of multiple sheet tabs at the same time
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. |
#3
|
|||
|
|||
edit names of multiple sheet tabs at the same time
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. |
#4
|
|||
|
|||
edit names of multiple sheet tabs at the same time
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. |
Thread Tools | |
Display Modes | |
|
|