Rename tab on cell value from another worksheet

[Admin]

Is this what you are after ?

Option Explicit

Sub RenameSheet()

    Dim Sht As Worksheet, ShtName As String
    
    On Error Resume Next
    With Worksheets("Sheet2")
        '//new sheet name
        ShtName = .Range("b2").Value
        '//check the sheet is already exists
        Set Sht = Worksheets(ShtName)
    End With
    If Sht Is Nothing Then
        ActiveSheet.Name = ShtName
    Else
        MsgBox "Sheet name '" & ShtName & "' already exist"
    End If

End Sub