Results 1 to 3 of 3

Thread: Sheet name

  1. #1

    Question Sheet name



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    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
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  3. #3
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    324
    Articles
    0
    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
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •