This post will guide you how to create a dynamic list of all worksheet names in your current workbook in Excel. How to create a dynamic list of worksheet names with Excel VBA macro.

• Create Dynamic List of All worksheet Names with Formula
• Create Dynamic List of All Worksheet Names with VBA

Assuming that you have an Excel Workbook that contain lots of worksheets, and you want to get a dynamic list of all the worksheet names in your current workbook. This post will explain two methods to create a dynamic list of worksheet names.

Create Dynamic List of All worksheet Names with Formula

---

Step1: go to Formulas tab in the Excel Ribbon, and click Define Name command under Defined Names group. And the New Name dialog will open.

Step2: enter “SheetList” in the Name field in the New Name dialog box. And then input the following formula:

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

Step3: click OK button, then enter number 1 in Cell A1, and number 2 in Cell A2.

Step4: select those two cells and drag them down to input sequence numbers, 3,4,5, etc. in Column A.

Step5: type the following formula in Cell B1, then the first worksheet name should be input in Cell B1.

Step6: drag the Cell B1 down until you see the #REF! error.

Create Dynamic List of All Worksheet Names with VBA

---

You can also use an Excel VBA Macro to achieve the same result of get a dynamic list of all worksheet names in your workbook, just do the following steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

Step4: paste the below VBA code  into the code window. Then clicking “Save” button.

Sub SheetNames()
    Columns(1).Insert
    For i = 1 To Sheets.Count
        Cells(i, 1) = Sheets(i).Name
    Next i
End Sub

Step5: back to the current worksheet, then run the above excel macro. Click Run button.

Step6: Let’s see the result: