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

[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

[jeremiah_j2k]

Hello Admin,

Sorry for the confusion because I guess I did not explain the problem clearly. From the attached file in post #1, the button and code will run in the "Main" tab. B2 is a dropdown list that contains the names of all the tabs that I need to rename based on a particular category in C2.

For example;

If "transport" is selected in C2, the sheet selected in B2 (e.g. Sheet4) will be named "bus".

The "bus" value is located in Xref!D2. The code should get the name of Sheet4 from Xref!D2


Thank you Admin

[Admin]

Still not clear

There is no value in C2, I think you are talking about A2

Now where is Sheet4 ? How could I know sheet4 is to be renamed ?

[jeremiah_j2k]

I have uploaded the wrong file in post #1 but already changed it with the correct one.

[Admin]

OK.

try

Sub RenameSheet()

    Dim Sht As Worksheet, ShtName As String, NewShtName As String
    
    On Error Resume Next
    With Worksheets("Main")
        '//new sheet name
        ShtName = .Range("b2").Value
        '//check the sheet is already exists
        Set Sht = Worksheets(ShtName)
        
        NewShtName = Evaluate("Xref!d2")
    
        If Not Sht Is Nothing Then
            Err.Clear
            Sht.Name = NewShtName
            If Not Err.Number = 0 Then
                MsgBox "Sheet name '" & NewShtName & "' already exist"
                Exit Sub
            End If
            .Columns(1).Replace ShtName, NewShtName, 1
        Else
            MsgBox "Sheet name '" & ShtName & "' not found"
        End If
    End With
    
End Sub

[jeremiah_j2k]

Thanks for the code admin... its working perfectly as needed and i really appreciate it.. thanks