Written by Allen Wyatt (last updated November 30, 2019)
This tip applies to Excel 97, 2000, 2002, and 2003
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.
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 (2216) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) 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!
Need to print a portion of a worksheet, but don't want to waste paper by printing the whole thing? It's easy to print ...
Discover MoreNeed the same print range set for different worksheets in the same workbook? It can't be done in one step manually, but ...
Discover MoreIf you are using a macro to create your printed Excel output, you may need a way to specify that paper should come from a ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon 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