How to List Sheet Name in Excel (5 Methods + VBA)

In some cases, you may have dozens of named sheets in your workbook. However, if you need the list of sheet names, you can utilize the formulas. In this article, I’ll discuss 5 conventional methods which are mainly formulas and also VBA code to find the sheet name list in Excel.

If you need to get the sheet name only instead of the list you can visit the Excel Sheet Name article. Here, we’ll show the process of retrieving the list of sheet names in a single column.

In the following figure, you see that we have 12 sheets which are mainly prices in months along a year. Also, the names of the sheets are like Price_Jan, Price_Feb, and so on.

Unfortunately, there is no dedicated function to get the list of sheet names in Excel. So we have to use the combination of several functions or the 2-step process of using Name Manager & formula.

Let’s dive into the methods.

1. Using the Combination of INDEX & FIND Functions to Get Sheet Name List

If you want to find the list of sheet names using several functions, you may use the formula containing the INDEX, LEFT, MID, and FIND functions.

Follow the steps below.

Steps:

⏩ Click on the Formulas tab > Name Manager option from the Defined Names ribbon.

⏩ Then you’ll see a dialog box namely Name Manager

⏩ Press on the New option.

⏩ Insert the Name (here the name is Worksheets) and the below formula in the Refers to section.

Note: GET.WORKBOOK is a macro-enabled function that stores the sheet names in the workbook.

⏩ After that enter the following formula in the B5 cell where you want to get the sheet names.

⏩ Finally, if you press Enter and use the Fill Handle Tool for the below cells, you’ll get the list of sheet names like the following.

Read More: How to Get Excel Sheet Name (2 Methods)

2. Utilizing the INDEX Function along with the REPLACE Function (Automated List)

Again, you can insert the below formula in the Refers to section and the Name as SheetNames after clicking the New option from the Name Manager dialog box.

Now, insert the formula where you want to get the list.

Here, B5 is the starting cell of the serial number (S/N).

3. Applying the TRANSPOSE Function to Find Sheet Name List

Furthermore, you can apply the TRANSPOSE function which returns a horizontal cell range as a vertical cell range, or vice versa.

Before doing that make sure the Name is GetSheetNames and insert the below formula.

Then insert the following formula.

Eventually, you’ll get the following output.

4. Using the LOOKUP Function to Get the Sheet Name List

Before using the LOOKUP function, create a new name where the Name may be Sheets and the formula in the Refers to section.

Note: The same macro-enable formula is used in the first method (Name: Worksheets), you can also utilze that if you want.

Then insert the formula.

If you press Enter and use the FIl Handle Tool, you’ll get the following output.

5. Creating a Dynamic List of Sheet Names Using SUBSTITUTE Function

Moreover, you may create a dynamic list of sheet names using the SUBSTITUTE function.

Fix the name as Dynamic_List_of_SheetNames and put the below formula.

Afterward, insert the following formula.

Here, B5 is the starting cell of the serial number (S/N).

6. Generating Sheet Name List Using VBA Code

Now, let’s see how you can apply the VBA code to get the list of sheet names in Excel.

Step 1: 

Firstly, open a module by clicking Developer>Visual Basic.

Secondly, go to Insert>Module.

Step 2: 

Then, copy the following code in your module.

Sub GetListOfAllSheets()
   Dim w As Worksheet
   Dim i As Integer
   i = 5
   Sheets("Price_Jun").Range("B:B").Clear
   For Each w In Worksheets
       Sheets("Price_Jun").Cells(i, 2) = w.Name
       i = i + 1
   Next w
End Sub

Step 3:

Finally, run the code.

Notes:

Be careful about the following facts while using the VBA code

• Worksheet name: Here, the worksheet name is “Price_Jun”.
• Cells(i, 2) means the cell location of row i (here, i=5) and column 2.

After running the code, the output will be as follows.

Read More: How to Search Sheet Name with VBA in Excel (3 Examples)

Things to Remember 

I. As the GET.WORKBOOK is a macro-enabled function, save the Excel file in .xlsm format (designated extension format for macro-enabled Excel file).

II. Be careful about the #BLOCKED error which may be found instead of the output if the workbook is unable to update.

Download Practice Workbook

Conclusion

In the above article, I tried to cover the methods to get the list of sheet names. Obviously, I believe these methods might be beneficial for you. Anyway, if you have any queries and suggestions, please don’t forget to share them in the following comments section.

Related Articles

• How to Search Sheet Name in Excel Workbook (2 Methods)
• Apply Sheet Name Code in Footer in Excel (3 Ways)
• How to Select Sheet by Variable Name with VBA in Excel (2 Ways)
• Use Sheet Name Code in Excel (4 Applications)
• How to Rename Sheet in Excel (6 Easy and Quick Methods)
• Rename Sheet with VBA in Excel (Both Single and Multiple Sheets)