List all sheets in an Excel workbook

Sometimes in Excel you need to create a list of all of the sheets in your excel workbook:

• if you have a large amount of sheets in a workbook you have created
• if you have hidden sheets
• if you have inherited a workbook from someone else and you wish to identify the contents

A simple piece of worksheet code will do the trick for you:

Sub ListSheetNames()
    Sheets.Add before:=Sheet1
    For n = 1 To Sheets.Count
        Cells(n, 2) = Sheets(n).Name
    Next n
End Sub

If you don’t need an extra sheet you can delete the line Sheets.Add before:=Sheet1

If you need to know which sheets are visible and which sheets are hidden insert the code below directly before the ‘Next n’ line

If Sheets(n).Visible = True Then Cells(n, 3) = “visible” Else Cells(n, 3) = “hidden”

You can also add extra detail for other sheet properties such as tab colour

Example use: I was recently given a spreadsheet for another department in a client business.  This spreadsheet had one tab for each customer (circa 120 customers) which listed all of the product sales by month.   A summary was needed of all the sales by product by month.   I used the code above to quickly provide me with a list of customers on the summary sheet I was creating.  Since all the customer sheets were identical then all that was required after this was a simple 3D sum formula.  If the sheets had not listed the products identically then I would have had to use this formula combination: SUMPRODUCT(SUMIF(INDIRECT)))

Go back to Useful Excel Hacks