Tom’s Tutorials For Excel: Listing Your Worksheets
Here are a few ways to list the names of the worksheets in your workbook.
For starters, you can right-click on the sheet navigation buttons to the left of your sheet tabs, and a list of sheets will appear. If there are many sheets, click the More Sheets item to see them all.
Another way to show that list is:
Step 1 – Press
Step 2 – Press
Step 3 – Type in
Step 4 – Press
Step 5 – Press
Alt+Q to return to your worksheet.
To list the worksheets programmatically, you can run this macro for example:
Sub ListSheets() Dim i% For i = 1 to Sheets.Count Cells(i, 1).Value = Sheets(i).Name Next i End Sub
Or, you can insert a new name in your workbook such as when you name a range.
Alt+I+N+D, but this time use this formula in the Refers To field:
and name it “Sheetz” (without the quotes).
Then, in some worksheet cell (A1 for example but it could be any cell) enter the formula
and copy or drag that formula down as needed.