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.
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.
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.
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.
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.
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.
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
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.
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.
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.
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.
To create a hyperlink for a block, first right-click on the block and select Link.
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!