Sheet name

pignick

New member
Joined
Apr 26, 2011
Messages
5
Reaction score
0
Points
0
Hi,

Does anyone out there know if it is possible to automatically rename a sheet by linking it to a cell in the sheet? I have created a consolidation workbook in to which I paste values from similar reports for different production sites and it would be useful to have the sheets re-named with the site names by reference to a cell in each sheet. (At present I re-name them manually).

Thanks, pignick
 

Roger Govier

New member
Joined
Mar 21, 2011
Messages
113
Reaction score
0
Points
0
Location
Located near Abergavenny, South Wales, UK
Hi

One way

Code:
Sub RenameSheets()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Sheets
    ws.Activate
        ws.Name = ActiveSheet.Range("A1").Value
    Next ws
End Sub

You could have it run through automatically every time you either opened or closed the workbook, so that any new sheets added were automatically renamed

Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
RenameSheets
End Sub
 

Simon Lloyd

Administrator
Joined
Apr 2, 2011
Messages
401
Reaction score
0
Points
0
Location
Manchester, England
Excel Version(s)
Excel 2016
Maybe it would be wise to check if the sheet name exists first :)
Code:
Sub Check_For_Sheet()
Dim Sh As Worksheet, ws As Worksheet
    On Error Resume Next
    For Each ws In Sheets
    Set Sh = ws.Range("A1").Value
        If Sh Is Nothing Then 'Doesn't exist
            Sh.Name = ws.Range("A1").Value
            Set Sh = Nothing
            On Error GoTo 0
        Else 'Does exist
            MsgBox "Sheet Name " & ws.Range("A1").Value & " does exist", vbInformation, "Found Sheet"
            Set Sh = Nothing
            On Error GoTo 0
        End If
        Next ws
End Sub
 
Top