VBA rename sheet based on cell value

1
votes

I want a VBA code that rename my sheetXXX, where XXX is the value in the cell B5 in “Sheet1” sheet. The macro should work for any value in B5.

I tried the following code:

Sub tabname()
    Dim sheetXXX As Worksheet
    XXX.Name = Worksheets("Sheet1").Range("B5").Value
End Sub
1
vbaexcel
Instead of using Worksheets("Sheet1") you should use ActiveSheet Propery to know more visit here – Stupid_Intern

1 Answers

1
votes

You have to set the object = to the actual worksheet to be renamed.

Sub tabname()
    Dim sheetXXX As Worksheet
    Set sheetXXX = ActiveWorkbook.Sheets("sheetXXX")
    sheetXXX.Name = "Sheet" & Worksheets("Sheet1").Range("B5").Value
End Sub

If sheetXXX is meant to be the active worksheet you would do this.

Sub tabname()
    Dim sheetXXX As Worksheet
    Set sheetXXX = ActiveWorkbook.Activesheet
    sheetXXX.Name = "Sheet" & Worksheets("Sheet1").Range("B5").Value
End Sub