How to create a list of all worksheet names from a workbook?

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 Kutools for Excel

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 Kutools for Excel

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