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