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

In some cases, you may have dozens of sheets in your workbook. However, if you need the list of sheet names, you can use Excel 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.

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 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 > Select the Name Manager option from the Defined Names ribbon.

⏩ Then you’ll see a dialog box named Name Manager.

⏩ Press on the New option.

⏩ Insert the Name (here the name is Worksheets) and insert 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 cell B5 where you want to get the sheet names.

⏩ Press Enter and use the Fill Handle tool for the below cells and you’ll get the list of sheet names like the following.

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 on cell C5.

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 this make sure the Name is GetSheetNames and insert the below formula.

⏩ Then insert the following formula and 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 use 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.

⏩ Set 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.

⏩ Open a module by clicking Developer > Visual Basic.

⏩ Go to Insert > Module.

⏩ Then, copy and paste the following code into 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

⏩ Finally, run the code and the output will be as follows.

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.

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 or suggestions, please don’t forget to share them in the following comments section.

Related Articles

• How to Get Excel Sheet Name
• How to Insert Excel Sheet Name from Cell Value
• How to Rename Sheet in Excel
• How to Use Sheet Name Code in Excel
• How to Apply Sheet Name Code in Footer in Excel
• How to Search by Sheet Name in Excel Workbook

<< Go Back to Excel Sheet Name | Excel Worksheets | Learn Excel