List All Sheets in Excel: Formula and VBA Solutions (10 Levels)
Last updated on Mar 08, 2024 By Victor Chan
Do you struggle to manage workbooks with dozens of sheets? Maybe you even have hundreds of sheets! Keeping track of all those sheets manually can be frustrating.
It could be that you want to quickly audit your workbook to make sure that all the expected sheets are there. Or you might want to identify and delete obsolete sheets to streamline file size.
Perhaps you're working with inventory management. And your sheets represent categories or locations for quick overview and access. You want to make sure they are all there.
Many Excel users assume that making a comprehensive list of all their sheets takes time. And has to be done by hand. This is a very tedious process. Not to mention error prone!
Don't waste your time and make mistakes. In this article, I'll show you how to leverage Excel's built-in features to compile a complete list of all your sheets.
From a secret formula to a powerful VBA solution, you'll gain valuable skills you can apply to your own Excel projects.
Not only will you generate an accurate list of sheets, you'll also learn how to create a clickable table of contents. Including back buttons for seamless navigation.
I've broken down this process into 10 levels to make it easier to follow. Let’s start!
Download FREE Excel Workbook
Step 1: Sign up for free Click Here
Step 2: Log in for access Click Here
Step 3: Download file List All Sheets - Formula and VBA.zip
Are you new to Excel VBA Macros?
• Find out how to enable and disable macros in Excel with this tutorial
• Save time and effort by copying VBA macros from one workbook to another with these instructions
Level 1: Manual Methods
If you prefer a manual approach, or just have a few sheets, you can right-click on the sheet tab area and select a list of sheet names, which you can copy and paste into Excel or a text document.
Alternatively, you can use the "View" menu and select "New Window," then "Arrange All" and "Arrange All Vertically" to display all sheet names in one window, side-by-side with another window that you use to write down the names.
However, these methods can be time-consuming and prone to errors, especially with a large number of sheets.
Level 2: The Secret Formula (GET.WORKBOOK)
Excel has a hidden gem called the GET.WORKBOOK function, an old Excel 4.0 macro function. While entering this function directly into a cell will produce an error, you can create a named range to make it work:
- 1. Go to the "Formulas" tab and select "Name Manager"
- 2. Click "New" and set the name as "GetSheets"
- 3. In the "Refers to" field, enter the formula: =GET.WORKBOOK(1)&T(NOW())
- Explanation of GET.WORKBOOK()
- =GET.WORKBOOK(type_num, name_text)
- GET.WORKBOOK is an old Excel 4.0 macro function. It cannot be typed in cells like other functions. It must be used in a named range.
type_num refers to workbook properties. Type_num 1 gives us the list of sheet names.
- name_text is the name of the workbook. Leave it blank and we get sheet names from the active workbook.
- Explanation of &T(NOW())
- &T(NOW()) adds a blank to the end of the result. Why?
- We do this to trigger automatic recalculation of the named range. This updates the list of sheet names.
- NOW() is a "volative" function and returns the current time. It triggers a recalculation of the defined name.
- The T function returns a blank when the value inside isn’t text. This means T() hides the time given by NOW().
- If we don't include this part, the named range does not automatically update when sheets are changed.
- 4. Click "OK" and close the "Name Manager."
- 5. In a cell, enter the formula: =TRANSPOSE(GetSheets)
If you have Excel 365 or Excel 2021, this formula will list all the sheet names in your workbook. It will spill down until all the sheets are listed.
However, it will also include the workbook name, which you may want to remove using the REPLACE function:
- 1. Create another named range called "SheetNames" with the formula: =REPLACE(GetSheets,1,FIND("]",GetSheets),"")
- 2. In a cell, enter the formula: =TRANSPOSE(SheetNames)
This will give you a clean list of all your sheet names.
If you have an older version of Excel that doesn't support spill ranges, you can use the ROW and INDEX functions instead:
- 1. In a column, enter the formula: =ROW(A1) and drag it down to populate row numbers. This is to show how ROW() works.
- 2. In the next column, enter the formula: =IFERROR(INDEX(SheetNames,ROW(A1)),"-")
This method will list all your sheet names, replacing any errors with a dash (-). And it will include “hidden sheets” whether you want to or not.
Also, you have to save the workbook as a Macro enable file (.xlsm) otherwise the GET.WORKBOOK function does not work.
And since we have to use a Macro enabled file with the formula method, why not just step straight into VBA? Especially since it’s not easy to not list “hidden sheets” using the formula method.
Level 3: VBA Solution - Single Column List
Now, let's take it a step further and automate the process using VBA. This solution will list all your sheets in a single column, and you can toggle the visibility of hidden sheets:
- 1. Right-click on the ribbon and select "Customize the Ribbon"
- 2. Check the "Developer" box and click "OK" to enable the "Developer" tab.
- 3. Go to the "Developer" tab and click "Visual Basic" (or press Alt+F11) to open the VBA editor.
- 4. Insert a new module (Click on menu “Insert” > Module)
- 5. In the code editor, paste the following code:
[VBA Code Box]
Sub Level_3_Single_Column()
' Set = False to Hide Hidden Sheets
' Set = True to Show Hidden Sheets
Const showHidden As Boolean = False
Dim wsList As Worksheet
Dim sheetIndex As Integer
Dim rowNumber As Integer
Set wsList = ThisWorkbook.Sheets.Add(Before:=ThisWorkbook.Sheets(1))
wsList.Name = "Sheet List"
' Set initial row number
rowNumber = 1
' Loop through all sheets and list their names based on visibility
For sheetIndex = 1 To ThisWorkbook.Sheets.Count
With ThisWorkbook.Sheets(sheetIndex)
' List the sheet if the name is not "Sheet List"
' and either "showHidden" is true or the sheet is visible
If .Name <> "Sheet List" And _
(showHidden Or .Visible = xlSheetVisible) Then
wsList.Cells(rowNumber, 1).Value = .Name
rowNumber = rowNumber + 1
End If
End With
Next sheetIndex
End Sub
- 6. Press F5 or click the "Run" button to execute the code.
This code will create a new sheet called "SheetList" and populate it with the names of all the other sheets in the workbook.
And it gives you a toggle to include or exclude any hidden sheets.
Set the constant showHidden to True to include hidden sheets or False to exclude them.
Make sure you save the workbook as a “Macro-enabled file” with the file extension “.xlsm” so that your VBA code is saved and works when you next open the file.
To find out more about enabling and disabling VBA code read How to Enable and Disable Macros in Excel: A Complete Guide.
Level 4: Adding Enhancements
To improve the appearance and functionality of the sheet list, you can add the following enhancements:
- 1. Add a bold header: "List of Sheets."
- 2. Add sheet numbering to help count the sheets.
- 3. Autofit the column width.
Here's the updated code with these enhancements:
[VBA Code Box]
Sub Level_4()
' Set = False to Hide Hidden Sheets
' Set = True to Show Hidden Sheets
Const showHidden As Boolean = False
Dim wsList As Worksheet
Dim sheetIndex As Integer
Dim rowNumber As Integer
Dim sheetCounter As Integer
Set wsList = ThisWorkbook.Sheets.Add(Before:=ThisWorkbook.Sheets(1))
wsList.Name = "Sheet List"
' Add bold header
With wsList.Range("A1")
.Value = "List of Sheets"
.Font.Bold = True
End With
' Set initial row number
rowNumber = 2 ' Start on second row to leave space for header
sheetCounter = 1
' Loop through all sheets and list their names based on visibility
For sheetIndex = 1 To ThisWorkbook.Sheets.Count
With ThisWorkbook.Sheets(sheetIndex)
' List the sheet if the name is not "Sheet List"
' and either "showHidden" is true or the sheet is visible
If .Name <> "Sheet List" And _
(showHidden Or .Visible = xlSheetVisible) Then
' Add sheet name and number to the list
wsList.Cells(rowNumber, 1).Value = sheetCounter & ". " _
& ThisWorkbook.Sheets(sheetIndex).Name
rowNumber = rowNumber + 1
sheetCounter = sheetCounter + 1
End If
End With
Next sheetIndex
' Autofit column
wsList.Range("A1").CurrentRegion.Columns.AutoFit
End Sub
This code will add a bold header "List of Sheets" in cell A1, number the sheets, and autofit the column width.
Make sure you save the workbook as a “Macro-enabled file” with the file extension “.xlsm” so that your VBA code is saved and works when you next open the file.
To find out more about enabling and disabling VBA code read How to Enable and Disable Macros in Excel: A Complete Guide.
Level 5: Error Checking
To prevent errors when a "SheetList" sheet already exists in the workbook, you can add error checking:
[VBA Code Box]
Sub Level_5()
' Check if "Sheet List" already exists and create if it doesn't
' Set = False to Hide Hidden Sheets
' Set = True to Show Hidden Sheets
Const showHidden As Boolean = False
Dim wsList As Worksheet
Dim sheetIndex As Integer
Dim rowNumber As Integer
Dim sheetCounter As Integer
' Check if 'Sheet List' already exists and select if it does
On Error Resume Next ' Ignore error if 'Sheet List' doesn't exist
Set wsList = ThisWorkbook.Sheets("Sheet List")
On Error GoTo 0 ' Resume normal error handling
' If 'Sheet List' doesn't exist, create it as the first sheet
If wsList Is Nothing Then
Set wsList = ThisWorkbook.Sheets.Add(Before:=ThisWorkbook.Sheets(1))
wsList.Name = "Sheet List"
Else
' Clear any existing content in 'Sheet List'
wsList.Cells.Clear
End If
' Add bold header
With wsList.Range("A1")
.Value = "List of Sheets"
.Font.Bold = True
End With
' Set initial row number
rowNumber = 2 ' Start on second row to leave space for header
sheetCounter = 1
' Loop through all sheets and list their names based on visibility
For sheetIndex = 1 To ThisWorkbook.Sheets.Count
With ThisWorkbook.Sheets(sheetIndex)
' List the sheet if the name is not "Sheet List"
' and either "showHidden" is true or the sheet is visible
If .Name <> "Sheet List" And _
(showHidden Or .Visible = xlSheetVisible) Then
' Add sheet name and number to the list
wsList.Cells(rowNumber, 1).Value = sheetCounter & ". " _
& ThisWorkbook.Sheets(sheetIndex).Name
rowNumber = rowNumber + 1
sheetCounter = sheetCounter + 1
End If
End With
Next sheetIndex
' Autofit column
wsList.Range("A1").CurrentRegion.Columns.AutoFit
End Sub
This code checks if the "SheetList" sheet already exists. If it does, it clears the existing content; otherwise, it creates a new sheet named "SheetList.
Make sure you save the workbook as a “Macro-enabled file” with the file extension “.xlsm” so that your VBA code is saved and works when you next open the file.
To find out more about enabling and disabling VBA code read How to Enable and Disable Macros in Excel: A Complete Guide.
Level 6: Wrapping the List
If you have many sheets, the list can extend beyond the visible area. To prevent this, you can set a maximum number of rows and wrap the list across columns:
[VBA Code Box]
Sub Level_6()
' Wrap to multiple columns
' Set bottom row for each column
Const MaxRows As Integer = 11
' Set = False to Hide Hidden Sheets
' Set = True to Show Hidden Sheets
Const showHidden As Boolean = False
Dim wsList As Worksheet
Dim sheetIndex As Integer
Dim rowNumber As Integer
Dim colNumber As Integer
Dim sheetCounter As Integer
' Check if 'Sheet List' already exists and select if it does
On Error Resume Next ' Ignore error if 'Sheet List' doesn't exist
Set wsList = ThisWorkbook.Sheets("Sheet List")
On Error GoTo 0 ' Resume normal error handling
' If 'Sheet List' doesn't exist, create it as the first sheet
If wsList Is Nothing Then
Set wsList = ThisWorkbook.Sheets.Add(Before:=ThisWorkbook.Sheets(1))
wsList.Name = "Sheet List"
Else
' Clear any existing content in 'Sheet List'
wsList.Cells.Clear
End If
' Add bold header
With wsList.Range("A1")
.Value = "List of Sheets"
.Font.Bold = True
End With
' Set initial numbers
rowNumber = 2 ' Start on second row to leave space for header
colNumber = 1 ' Start on the first column
sheetCounter = 1
' Loop through all sheets and list their names based on visibility
For sheetIndex = 1 To ThisWorkbook.Sheets.Count
With ThisWorkbook.Sheets(sheetIndex)
' List the sheet if the name is not "Sheet List"
' and either "showHidden" is true or the sheet is visible
If .Name <> "Sheet List" And _
(showHidden Or .Visible = xlSheetVisible) Then
' Add sheet name and number to the list
wsList.Cells(rowNumber, colNumber).Value = sheetCounter & ". " _
& ThisWorkbook.Sheets(sheetIndex).Name
rowNumber = rowNumber + 1
sheetCounter = sheetCounter + 1
' Check if we've hit the MaxRows limit
If rowNumber > MaxRows Then
' Reset row number and increase column number
rowNumber = 2
colNumber = colNumber + 1
End If
End If
End With
Next sheetIndex
' Autofit column
wsList.Range("A1").CurrentRegion.Columns.AutoFit
End Sub
This code sets the maximum number of rows to 11 and wraps the list across columns when the maximum row is reached.
Make sure you save the workbook as a “Macro-enabled file” with the file extension “.xlsm” so that your VBA code is saved and works when you next open the file.
To find out more about enabling and disabling VBA code read How to Enable and Disable Macros in Excel: A Complete Guide.
Level 7: Adding Padding
To improve readability, you can add padding to the columns:
[VBA Code Box]
Sub Level_7()
' Add padding for more space in each column
' Set bottom row for each column
Const MaxRows As Integer = 11
' Set = False to Hide Hidden Sheets
' Set = True to Show Hidden Sheets
Const showHidden As Boolean = False
Dim wsList As Worksheet
Dim sheetIndex As Integer
Dim rowNumber As Integer
Dim colNumber As Integer
Dim sheetCounter As Integer
' Check if 'Sheet List' already exists and select if it does
On Error Resume Next ' Ignore error if 'Sheet List' doesn't exist
Set wsList = ThisWorkbook.Sheets("Sheet List")
On Error GoTo 0 ' Resume normal error handling
' If 'Sheet List' doesn't exist, create it as the first sheet
If wsList Is Nothing Then
Set wsList = ThisWorkbook.Sheets.Add(Before:=ThisWorkbook.Sheets(1))
wsList.Name = "Sheet List"
Else
' Clear any existing content in 'Sheet List'
wsList.Cells.Clear
End If
' Add bold header
With wsList.Range("A1")
.Value = "List of Sheets"
.Font.Bold = True
End With
' Set initial numbers
rowNumber = 2 ' Start on second row to leave space for header
colNumber = 1 ' Start on the first column
sheetCounter = 1
' Loop through all sheets and list their names based on visibility
For sheetIndex = 1 To ThisWorkbook.Sheets.Count
With ThisWorkbook.Sheets(sheetIndex)
' List the sheet if the name is not "Sheet List"
' and either "showHidden" is true or the sheet is visible
If .Name <> "Sheet List" And _
(showHidden Or .Visible = xlSheetVisible) Then
' Add sheet name and number to the list
wsList.Cells(rowNumber, colNumber).Value = sheetCounter & ". " _
& ThisWorkbook.Sheets(sheetIndex).Name
rowNumber = rowNumber + 1
sheetCounter = sheetCounter + 1
' Check if we've hit the MaxRows limit
If rowNumber > MaxRows Then
' Reset row number and increase column number
rowNumber = 2
colNumber = colNumber + 1
End If
End If
End With
Next sheetIndex
' Autofit columns with extra space for padding
Autofit_Columns_On_Sheet ThisWorkbook.Sheets(wsList.Name), 1
End Sub
Sub Autofit_Columns_On_Sheet(ws As Worksheet, extraSpace As Single)
' To the same width as the widest column in the used space
Dim i As Integer
Dim col As Object
Dim maxWidth As Double
Application.ScreenUpdating = False
With ws.UsedRange
' Autofit all columns in the used range of the passed worksheet
.Columns.AutoFit
' Find the maximum column width
For Each col In .Columns
If col.columnWidth > maxWidth Then
maxWidth = col.columnWidth
End If
Next
' Add extra space for padding
.Columns.columnWidth = maxWidth + extraSpace
End With
Application.ScreenUpdating = True
End Sub
This code introduces a new subroutine AutoFit_Columns_On_Sheet that autofits the columns and adds extra space for padding. The extraSpace parameter determines the amount of padding (e.g. 3 for a padding of 3 characters).
Make sure you save the workbook as a “Macro-enabled file” with the file extension “.xlsm” so that your VBA code is saved and works when you next open the file.
To find out more about enabling and disabling VBA code read How to Enable and Disable Macros in Excel: A Complete Guide.
Level 8: Adding Hyperlinks
To create a clickable table of contents, you can add hyperlinks to each sheet name:
[VBA Code Box]
Sub Level_8()
' Turn into Table of Contents with hyperlinks
' Set bottom row for each column
Const MaxRows As Integer = 11
' Set = False to Hide Hidden Sheets
' Set = True to Show Hidden Sheets
Const showHidden As Boolean = False
Dim wsList As Worksheet
Dim sheetIndex As Integer
Dim rowNumber As Integer
Dim colNumber As Integer
Dim sheetCounter As Integer
' Check if 'Sheet List' already exists and select if it does
On Error Resume Next ' Ignore error if 'Sheet List' doesn't exist
Set wsList = ThisWorkbook.Sheets("Sheet List")
On Error GoTo 0 ' Resume normal error handling
' If 'Sheet List' doesn't exist, create it as the first sheet
If wsList Is Nothing Then
Set wsList = ThisWorkbook.Sheets.Add(Before:=ThisWorkbook.Sheets(1))
wsList.Name = "Sheet List"
Else
' Clear any existing content in 'Sheet List'
wsList.Cells.Clear
End If
' Add bold header
With wsList.Range("A1")
.Value = "List of Sheets"
.Font.Bold = True
End With
' Set initial numbers
rowNumber = 2 ' Start on second row to leave space for header
colNumber = 1 ' Start on the first column
sheetCounter = 1
' Loop through all sheets and list their names based on visibility
For sheetIndex = 1 To ThisWorkbook.Sheets.Count
With ThisWorkbook.Sheets(sheetIndex)
' List the sheet if the name is not "Sheet List"
' and either "showHidden" is true or the sheet is visible
If .Name <> "Sheet List" And _
(showHidden Or .Visible = xlSheetVisible) Then
' Create a hyperlink to the sheet
wsList.Hyperlinks.Add _
Anchor:=wsList.Cells(rowNumber, colNumber), _
Address:="", _
SubAddress:="'" & _
ThisWorkbook.Sheets(sheetIndex).Name & _
"'!A1", _
TextToDisplay:=sheetCounter & ". " & _
ThisWorkbook.Sheets(sheetIndex).Name
rowNumber = rowNumber + 1
sheetCounter = sheetCounter + 1
' Check if we've hit the MaxRows limit
If rowNumber > MaxRows Then
' Reset row number and increase column number
rowNumber = 2
colNumber = colNumber + 1
End If
End If
End With
Next sheetIndex
' Autofit columns with extra space for padding
Autofit_Columns_On_Sheet ThisWorkbook.Sheets(wsList.Name), 1
End Sub
Sub Autofit_Columns_On_Sheet(ws As Worksheet, extraSpace As Single)
' To the same width as the widest column in the used space
Dim i As Integer
Dim col As Object
Dim maxWidth As Double
Application.ScreenUpdating = False
With ws.UsedRange
' Autofit all columns in the used range of the passed worksheet
.Columns.AutoFit
' Find the maximum column width
For Each col In .Columns
If col.columnWidth > maxWidth Then
maxWidth = col.columnWidth
End If
Next
' Add extra space for padding
.Columns.columnWidth = maxWidth + extraSpace
End With
Application.ScreenUpdating = True
End Sub
This code replaces the previous sheet name listing with hyperlinks, allowing you to click on a sheet name and navigate directly to that sheet.
Make sure you save the workbook as a “Macro-enabled file” with the file extension “.xlsm” so that your VBA code is saved and works when you next open the file.
To find out more about enabling and disabling VBA code read How to Enable and Disable Macros in Excel: A Complete Guide.
Level 9: Adding Back Buttons
To quickly navigate back to the sheet list, you can add back buttons on each sheet:
[VBA Code Box]
Sub Level_9()
' Add Navigation Buttons
' Set bottom row for each column
Const MaxRows As Integer = 11
' Set = False to Hide Hidden Sheets
' Set = True to Show Hidden Sheets
Const showHidden As Boolean = False
Dim wsList As Worksheet
Dim sheetIndex As Integer
Dim rowNumber As Integer
Dim colNumber As Integer
Dim sheetCounter As Integer
' Check if 'Sheet List' already exists and select if it does
On Error Resume Next ' Ignore error if 'Sheet List' doesn't exist
Set wsList = ThisWorkbook.Sheets("Sheet List")
On Error GoTo 0 ' Resume normal error handling
' If 'Sheet List' doesn't exist, create it as the first sheet
If wsList Is Nothing Then
Set wsList = ThisWorkbook.Sheets.Add(Before:=ThisWorkbook.Sheets(1))
wsList.Name = "Sheet List"
Else
' Clear any existing content in 'Sheet List'
wsList.Cells.Clear
End If
' Add bold header
With wsList.Range("A1")
.Value = "List of Sheets"
.Font.Bold = True
End With
' Set initial numbers
rowNumber = 2 ' Start on second row to leave space for header
colNumber = 1 ' Start on the first column
sheetCounter = 1
' Loop through all sheets and list their names based on visibility
For sheetIndex = 1 To ThisWorkbook.Sheets.Count
With ThisWorkbook.Sheets(sheetIndex)
' List the sheet if the name is not "Sheet List"
' and either "showHidden" is true or the sheet is visible
If .Name <> "Sheet List" And _
(showHidden Or .Visible = xlSheetVisible) Then
' Create a hyperlink to the sheet
wsList.Hyperlinks.Add _
Anchor:=wsList.Cells(rowNumber, colNumber), _
Address:="", _
SubAddress:="'" & _
ThisWorkbook.Sheets(sheetIndex).Name & _
"'!A1", _
TextToDisplay:=sheetCounter & ". " & _
ThisWorkbook.Sheets(sheetIndex).Name
rowNumber = rowNumber + 1
sheetCounter = sheetCounter + 1
' Check if we've hit the MaxRows limit
If rowNumber > MaxRows Then
' Reset row number and increase column number
rowNumber = 2
colNumber = colNumber + 1
End If
End If
End With
Next sheetIndex
' Autofit columns with extra space for padding
Autofit_Columns_On_Sheet ThisWorkbook.Sheets(wsList.Name), 1
' Add Navigation Buttons
Call Add_Navigation_Buttons
End Sub
Sub Add_Navigation_Buttons()
Dim ws As Worksheet
Dim btn As Shape
Dim buttonExists As Boolean
' Loop through all sheets
For Each ws In ThisWorkbook.Sheets
' Skip the "Sheet List" worksheet
If ws.Name <> "Sheet List" Then
' Check if the button already exists
buttonExists = False
For Each btn In ws.Shapes
If btn.Name = "GoToSheetListButton" Then
buttonExists = True
' Delete existing button
btn.Delete
Exit For
End If
Next btn
' Add the button (shape, X, Y, Width, Height)
Set btn = ws.Shapes.AddShape(msoShapeRoundedRectangle, 5, 5, 75, 30)
With btn
.Name = "GoToSheetListButton"
.Fill.ForeColor.RGB = RGB(30, 30, 200) 'Blue color
With .TextFrame.Characters
.Text = "Sheet List"
.Font.Name = "Calibri"
.Font.Color = RGB(255, 255, 255) 'White color text
.Font.Size = 14
.Font.Bold = True
End With
.Line.Visible = msoFalse
End With
' Assign macro to button
ws.Shapes("GoToSheetListButton").OnAction = "Go_To_SheetList"
End If
Next ws
End Sub
Sub Go_To_SheetList()
ThisWorkbook.Sheets("Sheet List").Activate
ThisWorkbook.Sheets("Sheet List").Range("A1").Select
End Sub
Sub Autofit_Columns_On_Sheet(ws As Worksheet, extraSpace As Single)
' To the same width as the widest column in the used space
Dim i As Integer
Dim col As Object
Dim maxWidth As Double
Application.ScreenUpdating = False
With ws.UsedRange
' Autofit all columns in the used range of the passed worksheet
.Columns.AutoFit
' Find the maximum column width
For Each col In .Columns
If col.columnWidth > maxWidth Then
maxWidth = col.columnWidth
End If
Next
' Add extra space for padding
.Columns.columnWidth = maxWidth + extraSpace
End With
Application.ScreenUpdating = True
End Sub
This code adds a new subroutine Add_Navigation_Buttons that creates a rounded rectangle button on each sheet (except "Sheet List") with the text "Sheet List." When clicked, this button runs the Go_To_SheetList subroutine, which activates the "SheetList" sheet and selects cell A1.
Make sure you save the workbook as a “Macro-enabled file” with the file extension “.xlsm” so that your VBA code is saved and works when you next open the file.
To find out more about enabling and disabling VBA code read How to Enable and Disable Macros in Excel: A Complete Guide.
Level 10: Automatic Execution
Instead of manually running the code, you can set it to execute automatically when the "Sheet List" sheet is activated:
- 1. Double-click on the "Sheet List" sheet to open its code window.
- 2. Copy and paste the code from Level 9 and rename the main Sub to “List_All_Sheets_TOC”
- 3. In the code editor, go to the "Worksheet" module and select the "Activate" event.
- 4. Paste the following code inside the Worksheet_Activate event:
[VBA Code Box]
' LAUNCH EXCEL
' PRO TIP: Rename the code name of this sheet to Sheet0
' so that it appears at the top of the list of sheets
Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Call List_All_Sheets_TOC
Application.ScreenUpdating = True
End Sub
Sub List_All_Sheets_TOC()
' List all worksheets and add hyperlinks
' Set bottom row for each column
Const MaxRows As Integer = 11
' Set = False to Hide Hidden Sheets
' Set = True to Show Hidden Sheets
Const showHidden As Boolean = False
Dim wsList As Worksheet
Dim sheetIndex As Integer
Dim rowNumber As Integer
Dim colNumber As Integer
Dim sheetCounter As Integer
' Check if 'Sheet List' already exists and select if it does
On Error Resume Next ' Ignore error if 'Sheet List' doesn't exist
Set wsList = ThisWorkbook.Sheets("Sheet List")
On Error GoTo 0 ' Resume normal error handling
' If 'Sheet List' doesn't exist, create it as the first sheet
If wsList Is Nothing Then
Set wsList = ThisWorkbook.Sheets.Add(Before:=ThisWorkbook.Sheets(1))
wsList.Name = "Sheet List"
Else
' Clear any existing content in 'Sheet List'
wsList.Cells.Clear
End If
' Add bold header
With wsList.Range("A1")
.Value = "List of Sheets"
.Font.Bold = True
End With
' Set initial numbers
rowNumber = 2 ' Start on second row to leave space for header
colNumber = 1 ' Start on the first column
sheetCounter = 1
' Loop through all sheets and list their names based on visibility
For sheetIndex = 1 To ThisWorkbook.Sheets.Count
With ThisWorkbook.Sheets(sheetIndex)
' List the sheet if the name is not "Sheet List"
' and either "showHidden" is true or the sheet is visible
If .Name <> "Sheet List" And _
(showHidden Or .Visible = xlSheetVisible) Then
' Create a hyperlink to the sheet
wsList.Hyperlinks.Add _
Anchor:=wsList.Cells(rowNumber, colNumber), _
Address:="", _
SubAddress:="'" & _
ThisWorkbook.Sheets(sheetIndex).Name & _
"'!A1", _
TextToDisplay:=sheetCounter & ". " & _
ThisWorkbook.Sheets(sheetIndex).Name
rowNumber = rowNumber + 1
sheetCounter = sheetCounter + 1
' Check if we've hit the MaxRows limit
If rowNumber > MaxRows Then
' Reset row number and increase column number
rowNumber = 2
colNumber = colNumber + 1
End If
End If
End With
Next sheetIndex
' Autofit columns with extra space for padding
Autofit_Columns_On_Sheet ThisWorkbook.Sheets(wsList.Name), 1
' Add Navigation Buttons
Call Add_Navigation_Buttons
End Sub
Sub Add_Navigation_Buttons()
Dim ws As Worksheet
Dim btn As Shape
Dim buttonExists As Boolean
' Loop through all sheets
For Each ws In ThisWorkbook.Sheets
' Skip the "Sheet List" worksheet
If ws.Name <> "Sheet List" Then
' Check if the button already exists
buttonExists = False
For Each btn In ws.Shapes
If btn.Name = "GoToSheetListButton" Then
buttonExists = True
' Delete existing button
btn.Delete
Exit For
End If
Next btn
' Add the button (shape, X, Y, Width, Height)
Set btn = ws.Shapes.AddShape(msoShapeRoundedRectangle, 5, 5, 75, 30)
With btn
.Name = "GoToSheetListButton"
.Fill.ForeColor.RGB = RGB(30, 30, 200) 'Blue color
With .TextFrame.Characters
.Text = "Sheet List"
.Font.Name = "Calibri"
.Font.Color = RGB(255, 255, 255) 'White color text
.Font.Size = 14
.Font.Bold = True
End With
.Line.Visible = msoFalse
End With
' Assign macro to button
ws.Shapes("GoToSheetListButton").OnAction = "Go_To_SheetList"
End If
Next ws
End Sub
Sub Go_To_SheetList()
ThisWorkbook.Sheets("Sheet List").Activate
ThisWorkbook.Sheets("Sheet List").Range("A1").Select
End Sub
Sub Autofit_Columns_On_Sheet(ws As Worksheet, extraSpace As Single)
' To the same width as the widest column in the used space
Dim i As Integer
Dim col As Object
Dim maxWidth As Double
Application.ScreenUpdating = False
With ws.UsedRange
' Autofit all columns in the used range of the passed worksheet
.Columns.AutoFit
' Find the maximum column width
For Each col In .Columns
If col.columnWidth > maxWidth Then
maxWidth = col.columnWidth
End If
Next
' Add extra space for padding
.Columns.columnWidth = maxWidth + extraSpace
End With
Application.ScreenUpdating = True
End Sub
- 5. Optionally, rename the code name of the "Sheet List" sheet to "Sheet0" by going to the Properties window and changing the (Name) property.
Now, whenever you activate the "Sheet List" sheet, the code will automatically execute, listing all your sheets with hyperlinks and back buttons.
Ensure you save your workbook as a macro-enabled file to keep the VBA code. For a beginner’s guide on how to work with VBA macros in Excel, read this article How to Edit Macros in Excel: The Ultimate Beginner's Guide.
Conclusion
By following these 10 levels, you learned how to generate a comprehensive list of all your Excel sheets, complete with enhancements like sheet numbering, hyperlinks, back buttons, and automatic execution.
Whether you prefer the secret formula approach or the powerful VBA solution, you now have the tools to efficiently manage and navigate through your workbooks, even those with numerous sheets.
With practice, these new techniques will become second nature. Ensure you save your workbook as a macro-enabled file to keep the VBA magic intact.
I’d love to hear your feedback, so please share your experiences and questions in the comments below. Which part of the journey was your favorite?
I have a library of videos you can watch to learn more about Excel. You’ll learn new ways to improve your spreadsheets. Here’s the link to my YouTube channel.
And if you’d like to learn Excel VBA in a structured, methodical way, I have an online course called “Launch Excel Macros & VBA School”. You can find it in the shop.
Hi, I'm Victor!
Are you struggling with complex Excel tasks? Feeling overwhelmed by spreadsheets that are hard to use?
Many people believe mastering Excel is about learning shortcuts, functions, and formulas. But this overlooks the importance of building practical, real-world applications. It's not just about knowing the tools. It's about using them effectively.
That's where I come in. You'll get a unique perspective to Excel training from me. I have over 20 years of experience at Deloitte and two global tech companies. And I know what can make a difference in your career.
Let me help you integrate Excel into your professional life. Starting today. Read one of my articles, watch one of my videos. Then apply the new technique to your work. You'll see the difference immediately!
Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.
Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.
Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!
Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.
Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.
RECOMMENDED READING
Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.
Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!
Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.