Answered by:
rename multiple worksheets in same workbook

Question
-
Good afternoon all.
I have several workbooks which I need to copy a single worksheet multiple times, and then rename each worksheet, in numeric series.
Eg, sheet1 = 1, sheet2 = 2, etc....., sheet_n = n
I did record macro, and got the following, manually renumbering each one.
Sub RenameSheet()
'
' RenameSheet MacroSheets("1 (3)").Select
Sheets("1 (3)").Name = "3"
Sheets("1 (4)").Select
Sheets("1 (4)").Name = "4"
End Sub
I already have the copy multiple sheet macro, and it's working good. This macro just renames the sheets based on the source sheet's name. Eg., 1 becomes 1 (2), 1 (3), ....... 1 (n)
I just need to get one that renames all the sheets in numeric order.
TYIA.
Have a great weekend. I will be working on this for the rest of the afternoon, into early evening (I'm in PDT), if anyone is still available.
Friday, September 23, 2016 10:43 PM
Answers
-
Steve,
Re: what's your sheet name
Use a base name for the added worksheets that is not "Sheet".
That means you rename all your existing "Sheet1", "Sheet2" ... sheets.
It would be best if you use two digits as the suffix, as you may want to exceed 9 sheets.
In the code below, the sheets use a base name of "Obozo".
The MaxShtNum code finds the largest suffix number on all Obozo sheets and adds 1 to that number.
'---
Sub AddSheet()
ThisWorkbook.Worksheets.Add after:=ThisWorkbook.Worksheets(Worksheets.Count), Count:=1
ThisWorkbook.Worksheets(Worksheets.Count).Name = "Obozo" & VBA.Format(MaxShtNum(ThisWorkbook, "obozo"), "00")
End Sub
Function MaxShtNum(ByRef oWB As Excel.Workbook, ByRef strMatch As String) As Long
'Jim Cone - Portland, Oregon USA
On Error GoTo BadSheet
Dim oSht As Object
Dim M As Long
Dim N As Long
For Each oSht In oWB.Sheets
If VBA.InStr(1, oSht.Name, strMatch, vbTextCompare) > 0 Then
M = VBA.Val(Right$(oSht.Name, 2))
If M = 0 Then
M = VBA.Val(VBA.Right$(oSht.Name, 1))
End If
If M > N Then N = M
End If
Next 'oSht
MaxShtNum = N + 1
Set oSht = Nothing
Exit Function
BadSheet:
MaxShtNum = 0
Set oSht = Nothing
End Function
'---
Jim Cone
https://goo.gl/IUQUN2 (Dropbox)
- Edited by James Cone Saturday, September 24, 2016 2:10 AM added two digit number format
- Proposed as answer by Chenchen Li Wednesday, September 28, 2016 9:37 AM
- Marked as answer by Chenchen Li Friday, September 30, 2016 5:49 AM
Saturday, September 24, 2016 2:07 AM
All replies
-
Re: sheet names for several workbooks
Sub Name IsYourPosition()
Dim ws As Excel.Worksheet
Dim wb As Excel.Workbook
For Each wb In Workbooks
For Each ws In wb.Worksheets
ws.Name = ws.Index
Next
Next
End Sub
'---
Jim Cone
Portland, Oregon USA
https://www.dropbox.com/sh/ttybwg5e9r31twa/AAAnyBTHPX5XsTDp10ItTcw4a?dl=0
(free & commercial excel programs)Friday, September 23, 2016 11:08 PM -
Re: sheet names for several workbooks
Sub Name IsYourPosition()
Dim ws As Excel.Worksheet
Dim wb As Excel.Workbook
For Each wb In Workbooks
For Each ws In wb.Worksheets
ws.Name = ws.Index
Next
Next
End Sub
'---
Jim Cone
Portland, Oregon USA
https://www.dropbox.com/sh/ttybwg5e9r31twa/AAAnyBTHPX5XsTDp10ItTcw4a?dl=0
(free & commercial excel programs)
Hi James.
Long time no "see".
Thank you for your timely response.
I think I screwed up in my description....
I have 3 worksheets which do not need to be renamed, and several others which do.
The copy sheet macro copies a single numbered worksheet several times, giving each new sheet the name---
1 (2), 1 (3), 1 (4), 1 (5), ...... 1 (n), where (n) is the final sheet's number.
My goal is to renumber/rename, each newly copied sheet to its numbered series name.
eg.,
1, 2, 3, 4, .... n
So far, what appears to be happening is that the code runs through the names of the sheets, and then gets stuck on the fact that there's already a sheet named 1, and then throwing an error which states it cannot name a sheet the name of an already existing worksheet.
So, since I already have a sheet named 1, I suppose I need to start at the # 2, while not renaming the other 3 sheets, which have "normal (non-numeric)" names.
TYIA.
Friday, September 23, 2016 11:29 PM -
Steve,
Re: what's your sheet name
Use a base name for the added worksheets that is not "Sheet".
That means you rename all your existing "Sheet1", "Sheet2" ... sheets.
It would be best if you use two digits as the suffix, as you may want to exceed 9 sheets.
In the code below, the sheets use a base name of "Obozo".
The MaxShtNum code finds the largest suffix number on all Obozo sheets and adds 1 to that number.
'---
Sub AddSheet()
ThisWorkbook.Worksheets.Add after:=ThisWorkbook.Worksheets(Worksheets.Count), Count:=1
ThisWorkbook.Worksheets(Worksheets.Count).Name = "Obozo" & VBA.Format(MaxShtNum(ThisWorkbook, "obozo"), "00")
End Sub
Function MaxShtNum(ByRef oWB As Excel.Workbook, ByRef strMatch As String) As Long
'Jim Cone - Portland, Oregon USA
On Error GoTo BadSheet
Dim oSht As Object
Dim M As Long
Dim N As Long
For Each oSht In oWB.Sheets
If VBA.InStr(1, oSht.Name, strMatch, vbTextCompare) > 0 Then
M = VBA.Val(Right$(oSht.Name, 2))
If M = 0 Then
M = VBA.Val(VBA.Right$(oSht.Name, 1))
End If
If M > N Then N = M
End If
Next 'oSht
MaxShtNum = N + 1
Set oSht = Nothing
Exit Function
BadSheet:
MaxShtNum = 0
Set oSht = Nothing
End Function
'---
Jim Cone
https://goo.gl/IUQUN2 (Dropbox)
- Edited by James Cone Saturday, September 24, 2016 2:10 AM added two digit number format
- Proposed as answer by Chenchen Li Wednesday, September 28, 2016 9:37 AM
- Marked as answer by Chenchen Li Friday, September 30, 2016 5:49 AM
Saturday, September 24, 2016 2:07 AM