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

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *