none
rename multiple worksheets in same workbook RRS feed

  • 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.

    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)


    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)


    Saturday, September 24, 2016 2:07 AM