Neale Blackwood shows how to automate sheet naming
Q. Is it possible to link the sheet name to a cell's contents? I want the sheet name to be whatever I enter into cell A1.
A. There is no built-in Excel function or feature to do this. You have to use an event macro.
The macro below will change the sheet (tab) name to whatever is in cell A1. It won't change the sheet name if it isn't a valid name. It won't generate any error messages.
Private Sub Worksheet_Change(ByVal Target As Range) 'this macro renames the sheet with the value in
cell A1
Dim c As Range
On Error Resume Next
Set c = Intersect(Target, [A1])
If Not (IsEmpty(c)) Then
Me.Name = [A1].Value
End If
End Sub
To use this macro right click the sheet name and select View code. Type the above macro code in the white code area on the right-hand side of the Visual basic screen. You could copy the code via the online version of this article. Close the Visual basic screen and enter something in cell A1 to test the macro. To vary which cell the name is linked to, change all references in the code from A1 to the cell required. If you are unfamiliar with macro code it would be a good idea to practise on a blank file.
Warning: This method may break external links to the sheet involved. Any closed Excel files that are linked to a sheet will not update their links when a sheet name changes, resulting in broken links when the closed file is opened.
Neale Blackwood CPA is a senior business analyst with Access Analytic Solutions, which provides Excel consulting services.
Further information
Reference: March 2008, volume 78:02, p. 19
