Make an automatic list of tabs in an Excel workbook a better and more flexible way with options to create clickable links and sort the sheet list. Unlike older methods, it works in Excel for Windows and Mac.
We’ve already talked about fitting more tabs on the screen or making a manual list of tabs/worksheets. Now we’ll take the next step and make an automatic list of worksheets that will update as the workbook changes.
Making a list of worksheets is a thing you might expect to be easy but is almost ludicrously intricate. There’s no direct function to do show a list of worksheets, let alone a clickable list.
We’ll break it down so you can understand how the whole thing comes together. By the end of this article you’ll be able to make a list of visible, hidden or all worksheets displayed as either plain text or as clickable links and sorted.
Thanks to two of our regular Office Watchers, Ron S. and David Peel for their reminders, code and suggestions.
There are many ways to do this, we’ll explain others below but start with an easy nine-line VBA function.
Simple code to get an Excel sheet list
We liked this simple code and not just because it came from Office Watcher, David P <g>. All the code is at the bottom of this article.
Add this SheetList() code to a workbook module.
It creates an array with the names of the sheets which appear in worksheet along the row.
List sheets down a column
Want a list down the column? No problem, just add Transpose() to switch around the array.
List only visible sheets
The above code lists all sheets, visible or hidden. Adding two lines of code will limit the sheet list to just the visible ones; an IF statement and End If.
Want a list of only hidden sheets? Change the IF statement to:
If NOT sheet.Visible Then ‘ only show hidden sheets
with a matching End If before the Next statement.
Excel Sheets as links
Making clickable links is easy with the Hyperlink() function in Excel.
Add this formula into column B
=HYPERLINK("#'"&A7&"'!A1","Click Me")
- The full link looks like this “
#Groucho!A1” taking you to the A1 cell of the named sheet. - We’ve added the work ‘Click Me’ in the example to show that the visible cell text can be anything you like. To see just the tab names use
=HYPERLINK("#'"&A7&"'!A1",A7)
Drag and copy the cell down the column.
Gotchas and Extras
Sort Sheet list
Excel will display the sheet list in the order they appear along the tab row. If you’d prefer a different order just use Sort(). Replace the VBA function (e.g. SheetList() ) with this:
SORT(SheetList(),1,1,TRUE)
that will sort the array in ascending order.
For a sorted sheet list across a row use:
=SORT(SheetList(),1,1,TRUE)
and for a sorted column list
=TRANSPOSE(SORT(SheetList(),1,1,TRUE))
Save as a macro workbook
Because there’s VBA code the sheet can’t be saved in a ‘macro-free’ .xlsx workbook. If you try, an error appears.
Instead save as a macro-enabled workbook .xlsm . That’s a problem in some situations where macro-enabled Office documents are restricted or outright blocked due to security/virus risks.
Make the list automatically update
The formula, and the array list it makes, has a limitation. It’s only refreshed when Excel thinks it’s necessary, which isn’t good enough in this case. Changes to the tabs or names might not be quickly reflected in the array list.
There are a few ways to make a VBA function recalculate whenever there’s a change.
Application.Volatile
The easy option is making the VBA function ‘volatile’ by adding Application.Volatile to the function
That tells Excel to recalculate any cell containing this function. It’s probably overkill and may slow down larger worksheets but it’ll certainly keep the sheet list up to date.
I sometimes use Application.Volatile during development when tabs are being changed often but comment the line out once the workbook is stable.
Using &T(NOW())
Another way to make a cell update automatically needs an old Excel trick to force recalculations:
&T(NOW())
NOW() returns the current time and is automatically updated by Excel whenever it recalculates the worksheet. T() is a test for a text value with the useful property of returning an empty string ( “” ) if the function contents are NOT text. NOW() always returns a date/time (obviously) so &T(NOW()) will always add nothing to a string. Because the formula has the NOW() function, Excel will recalculate the entire name whenever a recalc occurs. In Excel parlance NOW() is a volatile function which makes the whole cell formula volatile.
=SheetList())&T(NOW()
=TRANSPOSE(SheetList())&T(NOW())
More sheet listing code options
There are other VBA macros to make a worksheet listing that have other options.
The Spreadsheet Guru has options to make a more nicely formatted list with numbered buttons, a multi-column list for many tab plus adding hyperlinks directly from code.
Microsoft has sample tabs list code too, thanks to Dennis Wallentin and Bill Jelen. These lists include page numbering.
Excel Campus has another method of getting tab names then creates a separate tab with a Table of Contents. Also another way to autoupdate the tabs list via Worksheet.Activate() There’s an accompanying video which also explains how to make a tabs list with thumbnail images of the sheets (start at about 5:30).
Code listings for SheetView()
Here’s the complete Excel sheet listing code both SheetList() and SheetListNoHidden() to keep hidden tabs off the list, thanks to David Peel.
Update: Thanks to Office Watcher Conrado M, added a line to explicitly set lngNumSheets to 1 if Option Base is set to 1 (the default is zero). LBound(Array()) returns 0 or 1 depending on Option Base setting ( Array() is not previously defined).
Function SheetList() As Variant
Application.Volatile
Dim sheet As Worksheet, strSheetList() As String, lngNumSheets As Long
If LBound(Array()) = 1 Then lngNumSheets = 1 ' IF Option Base setting is 1
For Each sheet In ActiveWorkbook.Worksheets
ReDim Preserve strSheetList(lngNumSheets)
strSheetList(lngNumSheets) = sheet.Name
lngNumSheets = lngNumSheets + 1
Next sheet
' returns a dynamic array along columns i.e 1 row x nn columns.
' use Transpose() to make a list down a single column i.e. nn rows x 1 column
SheetList = strSheetList()
End Function
Function SheetListnoHidden() As Variant
Dim sheet As Worksheet, strSheetList() As String, lngNumSheets As Long
If LBound(Array()) = 1 Then lngNumSheets = 1 ' IF Option Base setting is 1
For Each sheet In ActiveWorkbook.Worksheets
If sheet.Visible Then ' only show visible sheets
ReDim Preserve strSheetList(lngNumSheets)
strSheetList(lngNumSheets) = sheet.Name
lngNumSheets = lngNumSheets + 1
End If ' visible sheets
Next sheet
' returns a dynamic array along columns i.e 1 row x nn columns.
' use Transpose() to make a list down a single column i.e. nn rows x 1 column
SheetListnoHidden = strSheetList()
End Function
Automatic worksheet/tabs list in Excel – the old way
Quick Excel list sorting and filter buttons
