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!
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!
VBA Code:
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