Create a Table of Contents using Macros (VBA) in Excel Office 365!
Tags: createatableofconten, vbamacros
Published: 2021-02-08 10:27:01

---
title: Create a Table of Contents using Macros (VBA) in Excel Office 365!
published: true
date: 2021-01-30 09:06:38 UTC
tags: Createatableofconten,VBAMacros,createatableofconten
canonical_url: https://geekexcel.com/create-a-table-of-contents-using-macros-vba-in-excel-office-365/
---

Generally, the table of contents gives an overview of the document’s content. It allows users to directly move to the specific section in a document. In this article, we will learn the steps to **create a table of contents using Macros ([VBA](https://geekexcel.com/excel-office-365-vba-overview-definition-and-examples/))** in Excel Office 365. Let’s get into this article. Get an official version of ** MS Excel**  from the following link: [https://www.microsoft.com/en-in/microsoft-365/excel](https://www.microsoft.com/en-in/microsoft-365/excel)
 
## Steps to Create a Table of Contents using Macros:

To insert a table of contents using Macros in an Excel worksheet, follow the below steps.

- In the below image, you can see the list of [sheet](https://geekexcel.com/how-to-use-excel-sheet-function-in-office-365-with-examples/) tabs, and we are going to create a table of contents for these worksheets.

![Example](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/01/EXCEL_383PEXpITg.png)<figcaption id="caption-attachment-21515">Example</figcaption>

- On the **Developer Tab, select** the **Visual Basic** option under the **Code** section.

![Select Visual Basic option](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/09/Select-Visual-Basic-option-5.png)<figcaption id="caption-attachment-14839">Select the Visual Basic option</figcaption>

- Then you need to copy and paste the code given below.

VBA code to create a table of contents using Macros

Sub CreateTableOfContents()

Dim counter As Long

On Error Resume Next

Application.DisplayAlerts = False

‘If this worksheet already exists, redo this

Worksheets(“Table of Contents”).Delete

Application.DisplayAlerts = True

On Error GoTo 0

‘Add a new worksheet as our Table of Contents

ThisWorkbook.Sheets.Add Before:=ThisWorkbook.Worksheets(1)

ActiveSheet.Name = “Table of Contents”

‘Let us enumerate all of the worksheets in our ToC

For counter = 1 To Sheets.Count

‘Add one hyperlink for the specific sheet

ActiveSheet.Hyperlinks.Add \_

Anchor:=ActiveSheet.Cells(counter, 1), \_

Address:=””, \_

SubAddress:=”‘” & Sheets(counter).Name & “‘!A1”, \_

ScreenTip:=Sheets(counter).Name, \_

TextToDisplay:=Sheets(counter).Name

Next counter

End Sub

- You need to **save the code** by selecting it. Then **close** the **window.**

![Save the code](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/01/EXCEL_uPwaKI2IVD.png)<figcaption id="caption-attachment-21512">Save the code</figcaption>

- You have to open the sheet containing the data. On the **Developer Tab, choose** the **Macros option** in the **Code** section.

![Choose the Macros option](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/09/Choose-the-Macros-option-3.png)<figcaption id="caption-attachment-14841">Choose the Macros option</figcaption>

- You need to make sure that your macro is selected and **click** the **Run** button.

![Click Run button](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/01/EXCEL_TiYIGZE71s.png)<figcaption id="caption-attachment-21513">Click Run button</figcaption>

- Now, it will display the **table of contents** with all the worksheets name.
- You can click any worksheet name to straightly navigate to that specific worksheet.

![Output](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/01/EXCEL_2GyKSwRjGR.png)<figcaption id="caption-attachment-21514">Output</figcaption>

## Wind-Up:

In the above article, we have illustrated the steps to **create a table of contents using Macros ([VBA](https://geekexcel.com/excel-office-365-vba-overview-definition-and-examples/))** in Excel Office 365. Leave your **worthwhile feedback** with us in the below comment section. Thanks for visiting **[Geek Excel.](https://geekexcel.com/)** Keep Learning!