Forum Discussion

legendsaud's avatar
legendsaud
Copper Contributor
Jul 11, 2023

I want to Rename sheets based on a cell value - but not for all sheets.

I have the VBA to rename all sheets:

 

Sub RenameSheet()

Dim rs As Worksheet

For Each rs In Sheets
rs.Name = rs.Range("B2")
Next rs

End Sub

 

But I don't want it to work on all the sheets. It should exclude the sheets I don't want like the first one.

Plus if there is a way to do that along with automatically updating the sheet name as soon as the value changes - that would be awesome.

 

 

  • legendsaud 

    In the Visual Basic Editor, double-click ThisWorkbook under Microsoft Excel Objects.

    Copy the following code into the module:

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
        Select Case Sh.Name
            Case "Sheet1", "Sheet2"
                ' Skip these sheets - modify the names as needed
            Case Else
                If Not Intersect(Sh.Range("B2"), Target) Is Nothing Then
                    On Error Resume Next
                    Sh.Name = Sh.Range("B2").Value
                End If
        End Select
    End Sub

    This version will skip Sheet1 and Sheet2. You can change these names as needed, and add others if required.

Resources