Macro to Create a List of All Worksheets

This VBA macro adds a new sheet at the front of the workbook listing the name of every other sheet.

Works inExcel 2010 and later, Windows and Mac
UndoNot possible. Save a copy first
Save asMacro-Enabled Workbook (.xlsm)

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

List all sheet names

Sub ListSheetNames()
    Dim i As Long, out As Worksheet
    Set out = Worksheets.Add(Before:=Worksheets(1))
    out.Range("A1").Value = "Sheet name"
    For i = 2 To Worksheets.Count
        out.Cells(i, 1).Value = Worksheets(i).Name
    Next i
End Sub

The new sheet becomes sheet 1, so the loop starts at 2 to list every other sheet in order.

Include whether each sheet is hidden

Sub ListSheetsWithStatus()
    Dim i As Long, out As Worksheet
    Set out = Worksheets.Add(Before:=Worksheets(1))
    out.Range("A1:B1").Value = Array("Sheet name", "Visible")
    For i = 2 To Worksheets.Count
        out.Cells(i, 1).Value = Worksheets(i).Name
        out.Cells(i, 2).Value = (Worksheets(i).Visible = xlSheetVisible)
    Next i
End Sub

Column B shows TRUE for visible sheets and FALSE for hidden or very hidden ones.

Things to know

  • The list doesn’t update by itself. Run the macro again after adding or renaming sheets, after deleting the old list sheet.
  • For clickable links to every sheet, use the table of contents macro.
  • To just count the sheets, the formula =SHEETS() works without a macro.

Related macros