Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Printing a Worksheet List

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Printing a Worksheet List.

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.

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.

Related Tips:

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

 

Comments for this tip:

Dave Kerr    05 Dec 2013, 06:16
@Barry
Thanks for the tip about the sheet numbers. I didn't know how Excel handled them.
Neat solution too, thanks.
Barry Fitzpatrick    04 Dec 2013, 05:59
Here's an alternative that adds a sheet and names it "Sheet Names" to avoid any overwriting issues:

Sub List_of_Sheet_Names()

    Dim Number_of_Sheets As Integer
    Dim Row As Integer
    Dim Column As Integer
    Dim j As Integer
    Dim shtList As Worksheet
        
    'Delete an existing sheet named "Sheet Names"
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("Sheet Names").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    
    'create and name a new sheet, and position it at the far left
    Set shtList = Worksheets.Add(Before:=Sheets(1))
    shtList.Name = "Sheet Names"
    
    Number_of_Sheets = Sheets.Count
    'make the list of sheet names
    For j = 2 To Number_of_Sheets
        shtList.Cells(j - 1, 1) = Sheets(j).Name
    Next j
    
End Sub

NB if there is already a sheet named "Sheet Names" (e.g. if you've run the macro once already). then it will be deleted.

Note also the use of the "Before" parameter. This is because Excel sheet index number is in the order of the sheet tabs on the workbook itself i.e. the leftmost sheet tabs has index 1. The "Before" parameter positions the new sheet at the far left and so is not listed in the list of worksheets.
Dave Kerr    04 Dec 2013, 05:57
I went a little further and had the macro check that there are no currently filled cells in the range about to be occupied by the listing.

Excluding the declaration of variables, which should be fairly apparent, here is the code that I use now. It works well for me.

    NS = Sheets.Count
    R = ActiveCell.Row
    C = ActiveCell.Column
    NonBlank = 0
    
    For j = 1 To NS
        If Cells(R, C) <> "" Then
            NonBlank = NonBlank + 1
        End If
        R = R + 1
    Next j
    
    R = ActiveCell.Row
    
    If NonBlank > 0 Then
        MsgBox "This process will over-write existing cells." & vbCrLf & _
        "Please select an area of blank cells and re-start." & vbCrLf & _
            "Click OK to stop."
        Exit Sub
    Else
        For j = 1 To NS
            Cells(R, C).NumberFormat = "@"
            Cells(R, C) = Sheets(j).Name
            R = R + 1
        Next j
    End If


I hope this helps!
Dave
Chris    03 Dec 2013, 08:33
Thanks for this. Its just the start I needed to get what I want!

Altered it slightly and now the macro inserts the list of worksheets in the cell you pick.

Sub List_of_Sheet_Names()

    Dim Number_of_Sheets As Integer
    Dim Row As Integer
    Dim Column As Integer
    Dim j As Integer
        
    Number_of_Sheets = Sheets.Count
    Row = ActiveCell.Row
    Column = ActiveCell.Column
    
    For j = 1 To Number_of_Sheets
        Cells(Row, Column) = Sheets(j).Name
        Row = Row + 1
    Next j
    
End Sub

Barry Fitzpatrick    08 Oct 2012, 05:10
I would recommend in both of the above examples to clearing the column that will be used to store the sheet names, because if there is already data in the column that doesn't get overwritten by the macro then it will appear that there are more sheets than there actually are. Any subject macro where it to use the data in the column would give a run time error as it wouldn't be able to find the sheets at the end of the list (i.e. those entries that were not overwritten).
Dave K    08 Oct 2012, 03:33
Brilliant in its simplicity. Just add two lines and make one small change if you want to decide which column to use, as follows.

Sub GetSheets()
    Dim j As Integer
    Dim NS As Integer
    Dim C As Integer
    
    NS = Sheets.Count
    C = ActiveCell.Column
    
    For j = 1 To NS
        Cells(j, C) = Sheets(j).Name
    Next j
    
End Sub

Before you run the macro, select any cell in the column where you want the list to appear. The column value from ActiveCell is assigned to the variable C and used in the positioning loop.

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 3+4? (To prevent automated submissions and spam.)
 
          Commenting Terms
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2013)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2013)

Our Products

Premium Newsletters

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2014 Sharon Parq Associates, Inc.