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.
Leave a Reply