How to Get a Dynamic List of All Worksheet Names in Excel

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.

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.

create dynamic list of worksheets1

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

create dynamic list of worksheets2

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

create dynamic list of worksheets3

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

create dynamic list of worksheets4

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

create dynamic list of worksheets5

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

create dynamic list of worksheets6

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.

Get the position of the nth using excel vba1
Step2: then the “Visual Basic Editor” window will appear.

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

export each sheet to csv2

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

create dynamic list of worksheets7

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.

create dynamic list of worksheets8

Step6: Let’s see the result:

create dynamic list of worksheets9

 

 

You might also like:

Comments

So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sidebar