If I understand, the active sheet has existing sheet names in column A and new sheet names in column B. You want a macro that will rename each sheet listed in column A using the new name listed in column B. If the sheet in column A does not exist in the active workbook or the new name in column B is blank, it will be skipped.
' assume ActiveSheet has sheet names and renames
' assume sheet names in column A (1) and renames in column B (2)
' assume row 1 is a header and names begin in row 2
Dim sName As String, sRename As String, msg As String
Dim lastRow As Long, n As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
msg = vbNullString
On Error Resume Next
For n = 2 To lastRow
sName = Cells(n, 1)
sRename = Trim(Cells(n, 2))
If sRename <> vbNullString Then Sheets(sName).Name = sRename
If Err Then msg = msg & vbNewLine & sName
If msg <> vbNullString Then MsgBox "Could not rename:" & msg
Does this answer your question?