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 Macro

    Sheets("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.

Answer 1

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)

Answer 2

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.

Answer 3

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)