VBA: Rename tab name

[jbhamra89]

Hi SME's,

I found a code in the forums from Joe4 (Thank you for that) shown below.

I have a workbook with multiple tabs/worksheets. I need to rename the tabs based on a list in one of the tab - the tab is fixed and always called "Rename"

. The list has 2 columns - A and B. There are names of tabs in column B but they needs to be renamed by their corresponding cells in column A. e.g.

Column A
Compiled 2022-03-11

Column B
Compiled 2022-02-18

I am getting a "Subscript out of range" error, specifically in this line " Sheets(prevNm).Name = newNm"

Does anyone know what the issue could be? Thank you!

Sub MyRenameSheets()

    Dim lrow As Long
    Dim r As Long
  Dim prevNm As String
  Dim newNm As String
    
    Application.ScreenUpdating = False
    
'   Find last row in column A on Rename sheet with data
    lrow = Sheets("Rename").Cells(Rows.Count, "A").End(xlUp).Row
    
   ' On Error Resume Next
'   Loop through all rows on Rename sheet starting on row 2
    For r = 2 To lrow
'       Capture values
        prevNm = Sheets("Rename").Cells(r, "A")
        newNm = Sheets("Rename").Cells(r, "B")
'       Rename sheets
       Sheets(prevNm).Name = newNm
    Next r
   On Error GoTo 0
    
    Application.ScreenUpdating = True
        
End Sub

 

[Joe4]

Welcome to the Board!

That may mean it cannot find any sheets with that name listed in the "prevNm" field.
You can use Message Boxes to return what those variables contain, i.e.

MsgBox prevNm
MsgBox newNm

Verify that you actually do have a sheet with the name being returned by the "prevNm".
Note that it must match EXACTLY. Any extra spaces at the beginning of end will cause it not to match.

 

[mumps]

Try:

Sub MyRenameSheets()
    Dim LastRow As Long, rng As Range
    Application.ScreenUpdating = False
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each rng In Sheets("Rename").Range("B2:B" & LastRow)
        Sheets(rng.Value).Name = rng.Offset(, -1).Value
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[jbhamra89]

Try:

Sub MyRenameSheets()
    Dim LastRow As Long, rng As Range
    Application.ScreenUpdating = False
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each rng In Sheets("Rename").Range("B2:B" & LastRow)
        Sheets(rng.Value).Name = rng.Offset(, -1).Value
    Next rng
    Application.ScreenUpdating = True
End Sub

This worked perfectly! Thank you so much

 

[jbhamra89]

Welcome to the Board!

That may mean it cannot find any sheets with that name listed in the "prevNm" field.
You can use Message Boxes to return what those variables contain, i.e.

MsgBox prevNm
MsgBox newNm

Verify that you actually do have a sheet with the name being returned by the "prevNm".
Note that it must match EXACTLY. Any extra spaces at the beginning of end will cause it not to match.

Thank you for the response and for the base code! The updated code mumps provided fixed the issue

 

[mumps]

You are very welcome.
@Joe4 made a good point. This version of the macro will take care of the problem if it arises:

Sub MyRenameSheets()
    Dim LastRow As Long, rng As Range
    Application.ScreenUpdating = False
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each rng In Sheets("Rename").Range("B2:B" & LastRow)
        If Evaluate("isref('" & rng.Value & "'!A1)") Then
            Sheets(rng.Value).Name = rng.Offset(, -1).Value
        Else
            MsgBox ("Sheet " & rng.Value & " does not exist.")
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub