Rename tab on cell value from another worksheet

[jeremiah_j2k]

Hi All,

I need a help to make my code work. I'm trying to rename worksheet one at a time but i'm having problem in using cell reference. see my code below and thanks in advanced for your help

Sub rename()

Application.ScreenUpdating = False
On Error GoTo error_handler


If Range("C2") = "sports" Then
    Sheets(Format(Range("B2").Value)).Select
'    ActiveSheet.Name = need to get the active sheet's new name from Xref sheet D2
    On Error GoTo error_handler
    
Exit Sub
   

End If

error_handler:

    Sheets("Main").Select
    MsgBox ("Sheet name already exist")

Application.ScreenUpdating = True

End Sub