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 :confused:

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 :)