Create a Table of Contents for a List of Worksheets in Excel

This updated guide explains how to Create a Table of Contents for a List of Worksheets in Excel through clear steps, practical examples, and important checks that help prevent common errors.

Here are two ways to create a table of lists of sheets in Excel.

Method 1: Create a Table of Contents List of Sheets Manually.

Step 1: Create a new sheet should be placed at the top of the other sheets, where you create the list of sheets in any order you want.

Create a Table of Contents for a List of Worksheets in Excel � Method 1: Create a Table of Contents List of Sheets Manually.

Step 2: Select the first sheet name in the list you just created, right-click and select Hyperlink (or Ctrl + K key combination ).

Create a Table of Contents for a List of Worksheets in Excel � Method 1: Create a Table of Contents List of Sheets Manually.

Step 3: The Insert Hyperlink dialog box appears, select Place in This Document in the Link to section , then select the sheet name you may need to lead and click OK to finish.

Create a Table of Contents for a List of Worksheets in Excel � Method 1: Create a Table of Contents List of Sheets Manually.

Follow the same steps for the sheet names below, and you'll eventually get to the list of sheets in Excel. You just need to click the sheet name then the sheet you select will be opened.

Create a Table of Contents for a List of Worksheets in Excel � Method 1: Create a Table of Contents List of Sheets Manually.

But this is how you can use it with Excel files that have few sheets, if you have multiple sheets, you should do the following.

Method 2: Use VBA in Excel.

Step 1: On the Excel file, you need to create a table of contents for the list of sheets, press Alt + F11 to open the Visual Basic Editor .

Create a Table of Contents for a List of Worksheets in Excel � Method 2: Use VBA in Excel. screenshot 5

Step 2: On the Microsoft Visual Basic for Applications window, select Insert -> Module .

Create a Table of Contents for a List of Worksheets in Excel � Method 2: Use VBA in Excel. screenshot 6

Step 3: Copy the whole code below and paste it into the Module box.

Private Sub CreateTableOfContents () Dim wsSheet As Worksheet Dim ws As Worksheet Dim Counter As Long On Error Resume Next Set wsSheet = Sheets ("Mucluc") 'Check Check Sheet On Error GoTo 0 If wsSheet Is Nothing Then ' Neu chua added in the first place of Workbook Set wsSheet = ActiveWorkbook. Sheets. Add (Before: = Worksheets (1)) wsSheet. Name = "Mucluc" End If With wsSheet . Cells (2, 1) = "LIST SACH CAC SHEET " . Cells (2, 1). Name =" Index " . Cells (4, 1) . Value =" STT ". Cells (4, 2). Value =" Ten Sheet " End With 'Merge Cell With Range ("A2: B2") . Merge . HorizontalAlignment = xlCenter . Font. Bold = True End With 'Set ColumnWidth With Columns ("A: A"). ColumnWidth = 8 . HorizontalAlignment = xlCenter End With With Range ("A4") . HorizontalAlignment = xlCenter . Font. Bold = True End With Columns ("B: B"). ColumnWidth = 30 With Range ("B4") . HorizontalAlignment = xlCenter . Font. Bold = True End With Counter = 1 For Each ws In Worksheets If ws. Name <> wsSheet. Name Then 'Find the value of wShSheet. Cells (Counter + 4, 1). Value = Counter ' Create contact wsSheet. Hyperlinks. Add Anchor: = wsSheet. Cells (Counter + 4, 2), _ Address: = "", _ SubAddress: = ws. Name & "! A1", _ ScreenTip: = ws. Name, _ TextToDisplay: = ws. Name 'Add Button Return Sheet With New Sheet With ws . Hyperlinks. Add Anchor: =. Range ("H1"), Address: = "", SubAddress: = "Index", TextToDisplay: = "Dial ve " End With Counter = Counter + 1 End If Next ws Set xlSheet = Nothing End Sub

Step 4: Then you click Run -> Run Sub or F5 key to run the program.

Create a Table of Contents for a List of Worksheets in Excel � Method 2: Use VBA in Excel. screenshot 7

Now on your Excel file will have a Mucluc sheet and in it is a table of contents for the list of sheets in Excel. Which sheet you may need to open, all you need to do is click the mouse on the sheet name in the list of sheets.

Create a Table of Contents for a List of Worksheets in Excel � Method 2: Use VBA in Excel. screenshot 8

So with two ways of creating a table of contents for a list of sheets in Excel, hopefully you can apply it easily with your Excel file. Good luck!

FAQ

What is the easiest way to Create a Table of Contents for a List of Worksheets in Excel?

Start with the requirements in this guide, then follow each step in order. Check the result after every major change so problems are easier to identify.