How to Create Automatic Table of Contents for Sheets in Excel
How to Create Automatic Table of Contents for Sheets in Excel. Sometimes, you may have to work with hundreds or even thousands of sheets in an Excel file. Are you tired of constantly moving between different sheets? Then you can’t miss today’s article. Let’s learn how to create an automatic table of contents for sheets in Excel.
Display the Developer create Automatic Table
Usually, the Developer tab will not be displayed by default, similar to other common tabs such as Home, Insert, etc. Therefore, to display this tab, right-click on the ribbon and select Customize the Ribbon.
How to Create Automatic Table of Contents for Sheets in Excel
Then, the Excel Options dialog box will appear. Look to the right and you will see that the Developer option is not selected. Check that option and click OK. Now you will see the Developer tab at the top.
How to Create Automatic Table of Contents for Sheets in Excel
Get the Full Names of all Sheets
After displaying the Developer tab, we will start step by step to create an automatic table of contents for sheets in Excel. First, select Visual Basic on the Developer tab.
How to Create Automatic Table of Contents for Sheets in Excel
Then, the Microsoft Visual Basic for Applications dialog box will appear. Right-click on the name of the first sheet in the Microsoft Excel Objects section, select Insert, and then select Module to create a new module. The purpose of this operation is to create a file to write code.
How to Create Automatic Table of Contents for Sheets in Excel
Next, we will write the first code block to retrieve the index number and name of all the currently existing sheets. Please note that you should follow along with the video above to make it easier to follow the coding process. First, we declare the necessary variables: “wb” is an abbreviation for the main Workbook which is the Excel file that you are using, “ws” is an abbreviation for the current Worksheet that you are working on, “isheet” is a counter variable, and “irow” is a variable that stores the value of the row index of the cell. After declaring the necessary variables, we initialize with the two commands below.
How to Create Automatic Table of Contents for Sheets in Excel
Once the variables have been declared and initialized, we will create a loop to iterate through all the sheets. This loop will run from 2 (which is assigned to the “isheet” variable) to the total number of sheets that you have. First, the index number is obtained by subtracting 1 from the “isheet” variable, and the name of the sheet is obtained by using the command “wb.Sheets(isheet).Name”. Finally, you increment “irow” by 1 so that the next iteration of the loop will apply the values to the next row below. After you have completed this, press the green arrow button above to run the code block. At this point, your sheet’s table of contents should display the index number and name of each sheet.
How to Create Automatic Table of Contents for Sheets in Excel
Example:
isheet = 2
irow = 6
For isheet = 2 To wb.Sheets.Count
ws.Range(“B” & irow).Value = isheet – 1
ws.Range(“C” & irow).Value = wb.Sheets(isheet).Name
Next
Create links to sheets
The next step is to create links to the sheets from the table of contents. You can enter the code snippet shown below. The first line will select the current worksheet, the next line will select the cell where you want to insert the link, and the last line will create a hyperlink to the sheet you specify with the text “go to sheet”. Once you’re done, click the green arrow button at the top to run the code.
How to Create Automatic Table of Contents for Sheets in Excel
Example:
ws.Select
ws.Range(“D” & irow).Select
ws.Hyperlinks.Add Anchor:=Selection, Address:=””, SubAddress:=”‘” & wb.Sheets(isheet).Name & “‘!A1″, TextToDisplay:=”go to sheet”
Create a button to go back to the table of contents from each sheet
After creating the links, you should create a button to go back to the table of contents on each sheet to avoid having to scroll through hundreds or thousands of sheets. You can create a block by selecting Shapes from the Insert tab and choosing any block, as the button means “go back,” I will choose an arrow-shaped block.
How to Create Automatic Table of Contents for Sheets in Excel
To enter content for the block you just created, right-click on the block, choose Edit Text, and enter the desired text. You can also adjust the size, color, and alignment of this content.
How to Create Automatic Table of Contents for Sheets in Excel
The next step is quite important. You need to know the name of the block you just created because it will be necessary for the code we are about to execute. To see the name of the block, select it and look at the cell located in the upper left corner of the current sheet.
How to Create Automatic Table of Contents for Sheets in Excel
To create a hyperlink for a block, first right-click on the block and select Link.
How to Create Automatic Table of Contents for Sheets in Excel
At this point, the Insert Hyperlink dialog box will appear. Choose Place in This Document and then scroll up to select your table of contents sheet. Once done, click OK to create the hyperlink.
After following the steps above, you have created a button to return to the table of contents sheet, but it does not exist on other sheets yet. Therefore, we need to use an additional piece of code to create similar buttons on the remaining sheets. In the code below, the first line is to select the button that we created in the previous steps (which now has a hyperlink to the table of contents), the second line is to execute the copy command, the third line is to select the current sheet (based on the value of isheet in each loop), the fourth line is to select the cell, and the last line is to paste the button into the selected cell.
Once done, press the green arrow above to run the code. Good luck with your practice!
