• Microsoft Office
  • Microsoft Windows
  • Other Software
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us

No products in the cart.

  • Microsoft Office
  • Microsoft Windows
  • Other Software
  • Anti Virus
  • Blog
    • Word
    • Excel
    • Powerpoint
    • Software tricks/tips
  • POLICIES
    • PAYMENT GUIDE
    • SHIPPING POLICY
    • REFUND POLICY
    • TERMS & CONDITIONS
    • Contact Us
Excel

How to Create Automatic Table of Contents for Sheets in Excel

0 Comments

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

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

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

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

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

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

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

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

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

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

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

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.

Create Automatic Table 12After 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.

Create Automatic Table 13Once done, press the green arrow above to run the code. Good luck with your practice!

9
70 Views
Transfer from Excel to Word while preserving the original formattingPrevTransfer from Excel to Word while preserving the original formattingMarch 17, 2023
How to use Vlookup and Hlookup that few people know in ExcelMarch 18, 2023How to use Vlookup and Hlookup that few people know in ExcelNext

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts
  • Retrieve Data Based on Data Validation
    September 7, 2023
    Excel
  • How to Convert Multiple Rows to Columns and Rows in Excel?
    September 6, 2023
    Excel
  • VLOOKUP for Automatic Discount in Data Validation
    September 6, 2023
    Excel
  • How to Convert Text Dates to Dates in Excel?
    September 5, 2023
    Excel
Categories
  • Blog 80
  • Excel 754
  • Powerpoint 36
  • Software tricks/tips 128
  • Uncategorized 8
  • Word 109
Tags
the ISERROR
Top rated products
  • Kaspersky Small Office Security 15 PCs + 15 Mobiles + 2 Servers 1 Year Kaspersky Small Office Security 15 PCs + 15 Mobiles + 2 Servers 1 Year
    Rated 5.00 out of 5
    $201.00
  • Buy Windows 11 Professional MS Products CD Key
    Rated 5.00 out of 5
    $11.00
  • AVG Internet Security 2021 1 Device 1 Year Global AVG Internet Security 2021 1 Device 1 Year Global
    Rated 5.00 out of 5
    $11.00
  • Buy Office 2021 Professional Plus Key Global For 5 PC
    Rated 5.00 out of 5
    $45.00
  • Avast SecureLine VPN 2021 2 Years 5 Devices Global Avast SecureLine VPN 2021 2 Years 5 Devices Global
    Rated 5.00 out of 5
    $47.00
Products
  • Buy Office 2021 Professional Plus Key Global For 5 PC
    Rated 5.00 out of 5
    $45.00
  • Buy Office 2021 Professional Plus Key Global Bind To Your Microsoft Account
    Rated 4.83 out of 5
    $49.00
  • Windows Server 2022 Datacenter Key Global
    Rated 5.00 out of 5
    $15.00
  • Windows Server 2022 Standard Key Global
    Rated 4.47 out of 5
    $15.00
  • Windows Server 2022 Remote Desktop Services Device Connections (50) Cal Key Global
    Rated 5.00 out of 5
    $45.00

Buffcom.net always brings the best digital products and services to you. Specializing in Office Software and online marketing services

BIG SALE 50% IN MAY

Microsoft Office
Microsoft Windows
Anti-Virus
Contact Us

Visit Us:

125 Division St, New York, NY 10002, USA

Mail Us:

buffcom.net@gmail.com

TERMS & CONDITIONS | PAYMENT GUIDE  | SHIPPING POLICY  | REFUND POLICY

Copyright © 2019 buffcom.net  All Rights Reserved.