Tom’s Tutorials For Excel: Listing Your Worksheets

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 Alt+F11
Step 2 – Press Ctrl+G
Step 3 – Type in Application.CommandBars("Workbook Tabs").ShowPopup
Step 4 – Press Enter
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.
Press Alt+I+N+D, but this time use this formula in the Refers To field:
=SUBSTITUTE(GET.WORKBOOK(1),"["&GET.WORKBOOK(16)&"]","")
and name it “Sheetz” (without the quotes).
Then, in some worksheet cell (A1 for example but it could be any cell) enter the formula
=IF(COUNTA(Sheetz)>=ROW($A1),INDEX(Sheetz,ROW($A1)),"")
and copy or drag that formula down as needed.