Forum Discussion
legendsaud
Jul 11, 2023Copper Contributor
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.
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.