VBA to change tab name to a cell value on that tab

[lynnsong986]

Hello,

I'd like to have an event procedure (or a function) that can change each tab name automatically to a cell value on that tab. i.e. tab name will be changed to value in cell B6 on that tab for the entire workbook (many tabs in that workbook). Can someone please help?

thanks so much in advance.

Lynn

 

[Joe4]

You said that you want this to run "automatically".
So what exactly do you want to trigger this "automatic" process?

 

[Gary's Student]

Will each B6 contain a formula or a typed value ??

 

[Joe4]

Here is a Macro that can be run to update all tabs at once.

Sub RenameAllSheets()

    Dim ws As Worksheet
    Dim shtName As String
    
    On Error GoTo Err_chk
    For Each ws In ThisWorkbook.Sheets
        shtName = ws.Range("B6")
        If Len(Trim(shtName)) > 0 Then
            ws.Name = shtName
        Else
            MsgBox "Missing entry in cell B6 on sheet number " & ws.Index, vbOKOnly, "ERROR!"
        End If
    Next ws
    On Error GoTo 0
    
    MsgBox "Process complete!", vbOKOnly, "SUCCESS!"
    Exit Sub
        

Err_chk:
    If Err.Number = 1004 Then
        MsgBox "Invalid sheet name in cell B6 on sheet number " & ws.Index, vbOKOnly, "ERROR!"
    End If
    
End Sub

 

[lynnsong986]

right now the tabs all have a different name than the value in cell B6, so ideally I'd like to have the code changing the tab names to value in B6 first, then after that every time when that cell changes value (it is a value field, not formula based).

I've been using some inefficient code that first list all tab names (old) in a summary tab in column A, then I put the tab names I want to change to in column B, then I run another sub to loop through the workbook to change the tab names...I'm hoping for a better solution here.

thanks so much for responding!
Lynn

 

[lynnsong986]

thanks Joe4, can you help me with a worksheet_change event so that every time that value changes, the tab name will change automatically?

 

[Joe4]

thanks Joe4, can you help me with a worksheet_change event so that every time that value changes, the tab name will change automatically?

Yes I can, but you don't want to use Worksheet_Change, unless you want to paste that code in each and every sheet module in your workbook! You don't need to do that unnecessary repetition.
Instead, put the following code in the "ThisWorkbook" module, and it should work for all sheets.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    If Target.Address = "$B$6" And Target <> "" Then
        On Error GoTo Err_chk
        ActiveSheet.Name = Target
        On Error GoTo 0
    End If
    
    Exit Sub
    

Err_chk:
    If Err.Number = 1004 Then
        MsgBox "Invalid sheet name in cell B6 on sheet number " & ActiveSheet.Index, vbOKOnly, "ERROR!"
    End If
    
End Sub

 

[lynnsong986]

wow yes I was thinking I'll need to paste the code in each sheet! this is amazing if I don't need to...I'm trying it out and many many thanks Joe4!

 

[lynnsong986]

it works wonders...very much appreciated!

 

[Joe4]

You are welcome!
Glad I could help!