Make list of Excel sheets/tabs - automatic and even better

Here’s an even better way to make an automatic list of tabs in an Excel workbook it works in Excel for Windows and Mac and bypasses some possible traps.

Start with our article on Automatic worksheet/tabs list in Excel which explains how to add a list of Excel tabs/sheets with or without clickable links to make a ‘Table of Contents’ for an Excel workbook.

Also using Transpose() to change the list from horizontal (across a row) to vertical).

Plenty of people wrote in with their thanks, which should be directed to the coder: David Peel.

However, one Office Watcher, Conrado, politely pointed out a problem. If you have the Option Base is set to 1, the code didn’t work. Option Base tells Office to count arrays from either zero (the default and standard in most languages) or one. As David says “Excel has always been a bit weird in starting it’s collections from 1 rather than 0, so many people do like to do that for arrays, too.” by using Option Base 1.

We’ve added a fix to the original code to workaround this, relatively rare. problem. David Peel took that as an opportunity to revise the code to make a single Excel custom function to show all tabs or only visible tabs. The logic is changed so it doesn’t matter what the Option Base setting is and so no need for a special check.

Here’s another version of the Sheet List code with an optional parameter to choose showing hidden sheets or not.

Using SheetList()

To list only visible sheets (no hidden sheets) use SheetList() or SheetList(FALSE).

To make a list of all sheets use SheetList(TRUE).

That makes a dynamic array going across a row, for a list down a column use TRANSPOSE() e.g. TRANSPOSE(SheetList()).

To make the function automatically update (avoid for large workbooks) either add the line Application Volatile to the start of the function or use the &T(NOW()) trick, see Automatic worksheet/tabs list in Excel for more info.

SheetList() Code

Function SheetList(Optional ShowHidden As Boolean) As Variant
' Make list of Excel sheets / tabs to dynamic array.
' Code by David Peel

    Dim sht As Worksheet, strShtList() As String, lngNumSheets As Long

    For Each sht In ActiveWorkbook.Worksheets
        If Not sht.Visible Then   ' skip if sheet is hidden and ShowHidden is False.
            If Not ShowHidden Then GoTo Continue
        End If
        
        lngNumSheets = lngNumSheets + 1
        ReDim Preserve strShtList(1 To lngNumSheets)
        strShtList(lngNumSheets) = sht.Name

Continue:
    Next sht

    SheetList = strShtList()

End Function

Automatic worksheet/tabs list in Excel
Solved! The problem with Excel Tables and Transpose
BMI calcs can do more in Excel