Imagine you have a workbook with multiple worksheets and want to list all the sheet names automatically, complete with clickable hyperlinks to navigate to each corresponding sheet. This tutorial offers quick and effective methods for generating a list of worksheet names in Excel.

Get a list of all worksheet names from a workbook dynamically with formulas

Get a list of all worksheet names from a workbook with a useful feature

Get a list of all worksheet names from a workbook with VBA code

Get a list of all worksheet names from a workbook dynamically with formulas

In Excel, you can define a range name, and then apply a formula to list all sheet names from current workbook, please do with following steps:

1. Go to click Formula > Name Manager, see screenshot:

2. In the Name Manager dialog box, click New button, see screenshot:

3. In the popped out New Name dialog, specify a name into the Name text box, and then copy the below formula into the Refers to text box, see screenshot:

=GET.WORKBOOK(1)&T(NOW())

4. Then click OK > Close to close the dialog boxes, now, please go to a sheet where you want to list all the sheet names, and then enter the following formula into a blank cell:

=IFERROR(INDEX(MID(Sheetnames,FIND("]",Sheetnames)+1,255),ROWS($A$2:A2)),"")

Note: In the above formula, Sheetnames is the range name you are crested in step 3.

5. And then drag the fill handle down to the cells when blank cells are displayed, and now, all sheet names of current workbook have been listed as below screenshot shown:

6. If you want to create the hyperlink for each sheet, please use the below formula:

=HYPERLINK("#'"&A2&"'!A1","Go To Sheet")

Note: In the above formula, A2 is the cell that contains the sheet name, and A1 is the cell where you want the active cell to be located. For example, if you click the hyperlink text, it will locate the cell A1 of the sheet.

7. Now, when you click the hyperlink text, it will take you to that sheet, see below demo:

Get a list of all worksheet names from a workbook with a useful feature

With the Create List of Sheet Names utility of the third party add-in Kutools for Excel, you are able to create a list of worksheet names in one click, and link to each worksheet with hyperlink.

1. Click Kutools Plus > Worksheet > Create List of Sheet Names, see screenshot:

2. In the Create List of Sheet Names dialog box, please specify the following settings:

(1.) Choose sheet index styles you like, you can create the worksheet names with hyperlinks or with macro buttons. (2.) Enter the worksheet names for sheet index. (3.) Specify the location of the worksheet index. (4.) Specify how much columns you want to use in the new worksheet to display the worksheet names.

3. After finishing the settings, then click OK. All of the worksheet names have been listed with links in a new worksheet of the current workbook. See screenshot:

worksheet names with hyperlinks	worksheet names with macro buttons

Get a list of all worksheet names from a workbook with VBA code

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following macro in the Module Window.

VBA: List all worksheet names with hyperlinks in a new worksheet:

Sub CreateIndex()
'updateby Extendoffice
    Dim xAlerts As Boolean
    Dim I  As Long
    Dim xShtIndex As Worksheet
    Dim xSht As Variant
    xAlerts = Application.DisplayAlerts
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("Index").Delete
    On Error GoTo 0
    Set xShtIndex = Sheets.Add(Sheets(1))
    xShtIndex.Name = "Index"
    I = 1
    Cells(1, 1).Value = "INDEX"
    For Each xSht In ThisWorkbook.Sheets
        If xSht.Name <> "Index" Then
            I = I + 1
            xShtIndex.Hyperlinks.Add Cells(I, 1), "", "'" & xSht.Name & "'!A1", , xSht.Name
        End If
    Next
    Application.DisplayAlerts = xAlerts
End Sub

3. Press the F5 key to run this macro. All worksheet names from the active workbook are now listed in a new worksheet named "Index," with hyperlinks that navigate to each respective sheet. See the screenshot below:

Related articles