Cara menggunakan vba list sheet names

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.

Table of Contents

Download Practice Workbook

Sheet Name List.xlsm

How to Get Sheet Name List in Excel

In the following figure, you see that we have 12 sheets which are mainly prices in months along a year. Also, the names of 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 Function 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.

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

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))

⏩ Finally, if you press Enter and use 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 INDEX Function along with 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)),"")

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

3. Applying 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)

Then insert the following formula.

=TRANSPOSE(GetSheetNames)

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.

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

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))))

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.

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

Afterward, insert the following formula.

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

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

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

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.

Bagaimana cara memunculkan sheet di excel?

Jelaskan apa yang anda pahami mengenai fitur VBA pada ms excel?

Apa itu kode VBA?

Bagaimana cara langkah membuka VBA di Excel?