Using VBA to rename select worksheets based upon a cell value in each sheet?

[fserrano]

Hello ALL!

I am a VBA greenhorn and need assistance in renaming (11) select worksheets in an excel workbook that containd based upon a cell value in each sheet.

This forum does have several posts relating to this same problem; however, no posts address naming specific sheets as well as where to place the code in VBA, or how the code is triggered.

The sheets that need to be renamed are as follows:

Sheet2, Sheet3, Sheet7, Sheet8, Sheet9, Sheet10, Sheet11, Sheet12, Sheet13, Sheet14 and Sheet15.

I would like to change the "Sheet Name" to the value of cell "G1" from each respective sheet.

Please let me know if the code has to go into each respective object in VBA or if it can be entered as a Module. Is there any way to have the code fire anytime the "G1" value changes in each sheet so that it is updated "real time" instead of having to save and reopen the workbook?

Thank you for your help!!

 

[bbott]

Try this in each sheet you want changed:

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False
    Target = Range("G1")

    If Range("G1") = "" Then
        MsgBox ("Sheet name cannot be blank")
        Application.EnableEvents = True
        Exit Sub
    End If
    
    ActiveSheet.Name = Range("G1").Value
    Application.EnableEvents = True
    
End Sub

 

[BobUmlas]

In a regular module, to be run once to kick start the thing:

Sub GUpdate()
On Error Resume Next
For Each Sh In Sheets
Select Case Sh.CodeName
Case "Sheet2", "Sheet3", "Sheet7", "Sheet8", "Sheet9", "Sheet10", _
"Sheet11", "Sheet12", "Sheet13", "Sheet14", "Sheet15"
Sh.Name = Sh.Range("G1").Value
End Select
Next
End Sub

In the ThisWorkbook code( so it happens when any G1 is changed on the wanted sheets):
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Select Case Sh.CodeName
Case "Sheet2", "Sheet3", "Sheet7", "Sheet8", "Sheet9", "Sheet10", _
"Sheet11", "Sheet12", "Sheet13", "Sheet14", "Sheet15"
If Target.Address = "$G$1" Then Sh.Name = Range("G1").Value
End Select
End Sub

 

[fserrano]

Awesome!!

I input the codes as directed and hit F5 then chose the macro. This worked one time but will not update the when "G1" changes. I have to enter VBA and hit F5 when I want the tab names to update, is there a way to make it more dynamic?