Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Printing a Worksheet List.
Written by Allen Wyatt (last updated May 21, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365
In complex workbooks that contain many worksheets, it is not unusual to need a list of the different worksheets. Once you have the list, you can print it or use it in some other fashion, such as to create a table of contents for your workbook. The following macro, GetSheets, quickly retrieves the names of the worksheets in the current workbook. It places them in the current worksheet, starting at cell A1 and then working downwards.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here:
Sub GetSheets()
Dim j As Integer
Dim NumSheets As Integer
NumSheets = Sheets.Count
For j = 1 To NumSheets
Cells(j, 1) = Sheets(j).Name
Next j
End Sub
This macro will overwrite anything in a cell it needs in the current workbook, so you should make sure you don't need anything in column A of the worksheet. If you don't want to overwrite anything, make sure you create a new worksheet and then run the macro from that worksheet.
Once the list of worksheets is created, you can format it as desired, and then print it out.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12181) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Printing a Worksheet List.
Best-Selling VBA Tutorial for Beginners Take your Excel knowledge to the next level. With a little background in VBA programming, you can go well beyond basic spreadsheets and functions. Use macros to reduce errors, save time, and integrate with other Microsoft applications. Fully updated for the latest version of Office 365. Check out Microsoft 365 Excel VBA Programming For Dummies today!
Excel is rather weak on giving you control over how page numbers appear on a printout. This is never more apparent than ...
Discover MoreIf you want to print multiple copies of a worksheet using a different footer for each copy, the easiest way is to rely on ...
Discover MoreSometimes it is just easier to use the keyboard than it is to use the mouse. If you are a keyboard-oriented person, you ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2022-05-21 11:19:18
J. Woolley
My Excel Toolbox includes the following dynamic array function:
=ListSheets([SkipHidden])
This function returns one row with sheet names in subsequent columns. To return the list in one column with several rows, use this:
=TRANSPOSE(ListSheets([SkipHidden]))
In older versions of Excel that do not support dynamic arrays, you can use ListSheets with the SpillArray function like this:
=SpillArray(TRANSPOSE(ListSheets([SkipHidden])))
SpillArray will determine and populate the spill range for its array expression argument, simulating a dynamic array.
My Excel Toolbox also includes the following function:
=SheetListUpdateLink([Friendly_Name],[Screen_Tip])
This function uses SuperLink to create a hyperlink that will list a workbook's sheets (including hidden sheets) in subsequent rows. Each sheet in the list includes a hyperlink to hide or activate (unhide) the sheet.
See https://sites.google.com/view/MyExcelToolbox
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments