Create a table of contents for a list of worksheets in Excel

Your Excel spreadsheet has many sheets, you want to manage them in the most scientific way so they can quickly open when needed. To manage the sheets quickly and easily, you need to create a table of contents of the list of sheets in the Excel file.

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 Picture 1

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 Picture 2

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

Create a table of contents for a list of worksheets in Excel Picture 3

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 on the sheet name then the sheet you select will be opened.

Create a table of contents for a list of worksheets in Excel Picture 4

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 Picture 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 Picture 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 Picture 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 want 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 Picture 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!