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

Get FREE Advanced Excel Exercises with Solutions!

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.

Dataset

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.

Opening Name Manager

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

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

Creating a New Name

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.

=INDEX(MID(Worksheets,FIND("]",Worksheets)+1,255),ROWS($B$5:B5))

Sheet Name List Using INDEX & FIND Functions

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

Sheet Name List Using INDEX & FIND Functions

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.

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

Sheet Name List Utilizing The INDEX Function

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

=INDEX(SheetNames,B5)

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

Sheet Name List Utilizing The INDEX Function


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.

=GET.WORKBOOK(1)

Applying TRANSPOSE Function to Find The Sheet Name List

Then insert the following formula.

=TRANSPOSE(GetSheetNames)

Eventually, you’ll get the following output.

Applying TRANSPOSE Function to Find The Sheet Name List


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.

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

Using the LOOKUP Function to Get the Sheet Name List

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.

=LOOKUP("xxxxx",CHOOSE({1,2},"",INDEX(MID(Sheets,FIND("]",Sheets)+1,255),ROWS(B$1:B1))))

Excel Sheet Name List Using the LOOKUP Function

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

Using the LOOKUP Function to Get the Sheet Name List


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.

=SUBSTITUTE(GET.WORKBOOK(1),"["&GET.WORKBOOK(16)&"]","")

Creating a Dynamic List of Sheet Name Using SUBSTITUTE Function

Afterward, insert the following formula.

=INDEX(Dynamic_List_of_SheetNames,B5)

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

Creating a Dynamic List of Sheet Name Using SUBSTITUTE Function


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.

How to Insert VBA Code

Secondly, go to Insert>Module.

How to Insert VBA Code

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

Sheet Name List Using VBA Code

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.

Sheet Name List Using VBA Code

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

Md. Abdul Kader
Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo