Create a List of Worksheet Names
Creating a list of worksheet names is relatively easy using the CELL function/formula. In any existing saved workbook already loaded with data, create a neww orksheet named Worksheets, and add the heading Names to A1. Now, in cell A2 enter the following formula:
=CELL("filename",Sheet1!$A$1)
where Sheet1 is the name of the first worksheet in your workbook (excluding the one we just added and named Worksheets).
This formula will return the file path, workbook name, and worksheet name. We will pull out what we need (the worksheet name) soon.
Now, copy this formula down as many rows as you have worksheets. Change each occurrence of Sheet1 to the names of your other worksheets. Leave !$A$1 as is.
We referenced A1 (can be any cell) on each specific sheet, so that our CELL formulas/functions change when/if the worksheet name changes. Also, if no worksheet is specified (e.g., =CELL("filename",A1)), the worksheet name will always be the current active worksheet. This will be an issue when we reference the list from another worksheet, because the CELL function will return the worksheet name of the worksheet housing the formula unless a cell reference is supplied.
