Use a List of Existing worksheet names and update them based on new list

[jamiecobbey]

I have a list of existing worksheet names in A1:A50 (although the worksheets themselves are NOT in that order) and a list of new names I want to update each sheet to in B1:B50. I would like to run a macro that looks for the worksheet name in A1, finds it in the workbook and then renames it to B1 and then continue down the list.

First time posting here - really appreciate any help

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub ChangeNames()
   Dim Cl As Range
   
   For Each Cl In Sheets("[COLOR=#ff0000]List[/COLOR]").Range("A1:A50")
      If Not IsEmpty(Cl) Then
         If Evaluate("isref('" & Cl.Value & "'!a1)") Then Sheets(Cl.Value).Name = Cl.Offset(, 1).Value
      End If
   Next Cl
End Sub

Change sheet name in red to suit

 

[JoeMo]

Run this with the sheet your list is on the active sheet:

Sub RenameSheets()
Dim V As Variant, i As Long
V = ActiveSheet.Range("A1:B50").Value
Application.ScreenUpdating = False
For i = LBound(V, 1) To UBound(V, 1)
    On Error Resume Next
    Sheets(V(i, 1)).Name = V(i, 2)
    On Error GoTo 0
Next i
Application.ScreenUpdating = True
End Sub