How To List All Worksheets Name In A Workbook

Background

Sometimes you would like to get a list of all worksheets’ names in a workbook.

• To create a content page (or use this content page creation tool)
• To Change Sheet Reference In Copied Formulas
• To show your boss how much work have you done

In this article, I will show you 3 ways to list all worksheets’ name in a workbook.

Example

In this article, we will be using this workbook as an example.

Sample Workbook

This is a workbook with 12 tabs.

You can download the workbook to practice it by yourself.

Expected Outcome

Option 1 Manual Input

I believe most people are listing worksheets’ name this way.

I only input manually when the number of worksheets is small. If you have a workbook with hundreds of tabs, I will recommend you using other ways.

This is because manual input is prone to error and mistakes. Space or a dot can make a significant difference especially when you need to reference the cell.

Option 2 Name manager and formula

Step 1: Choose “Formula” tab

Step 2: In “Defined names” group, select “Name manager“

Step 3: Press “New” in the “Name Manager” dialog

Step 4: Input a name (In this example, I named it as “Worksheet_name”)

Step 5: Input the below formula in the “Refers to: ” box

=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

Step 6: Press “OK”

Step 7: Press “Close”

Step 8: Create number sequence

The largest number should equal to number of tabs in your workbook.

Tips: You can enter number larger than that and delete it later.

Since I know how many sheets does my workbook contain, I will create the number sequence up to 12.

Step 9: Input the formula in Cell A2

=INDEX(Worksheet_name,1)

Step 10: Drag your formula down

Done!

Option 3 VBA

Read How to Insert & Run VBA code in Excel – VBA101 if you forget how to insert VBA code in Excel.

Sub getworksheetname()
'The script is created by Dollar Excel.
'Subscribe to Dollar Excel newsletter for more free resources like this.

Dim ws As Worksheet
    For Each ws In Worksheets
        'You can change the column
        Range("A300").End(xlUp).Offset(1, 0).Value = ws.Name
    Next
End sub

Customize your VBA code

The below script will print your list of worksheets’ name in column A.

To print the list in column B

Range("B300").End(xlUp).Offset(1, 0).Value = ws.Name

To Print the worksheet name every two rows

Range("B300").End(xlUp).Offset(2, 0).Value = ws.Name

Do you find this article helpful? Subscribe to our newsletter to get exclusive Excel tips!