zakruti.com » IT - Software » Geeks Tutorial

How to Create a Table of Contents in Excel 2016

video description

Rating: 4.0; Vote: 1

were going to show you how to create a table of contents in Excel 2016. Don't forget to check out our main channel for more free how-to videos! Join us on Facebook Our App The easiest way to do this especially if you only have a few worksheets is to select a cell and hit Ctrl+K to create a hyperlink to a selected worksheet. The next method uses VBA to automatically create the hyperlinks to all the worksheets. Go to Options. Click Customize Ribbon and check the Developer box. Under the Developer tab, click Visual Basic. Click Insert then click Module. Copy and paste the following code: Option Explicit Sub Create_ToC) Dim wbBook As Workbook Dim wsActive As Worksheet Dim wsSheet As Worksheet Dim lnRow As Long Set wbBook = ActiveWorkbook With Application . DisplayAlerts = False . ScreenUpdating = False End With On Error Resume Next With wbBook . Worksheets(ToC. Delete . Worksheets. Add Before: =. Worksheets(1) End With On Error GoTo 0 Set wsActive = wbBook. ActiveSheet With wsActive . Name = ToC With. Range(A1) . Value = VBA. Array(Table of Contents) . Font. Bold = True End With End With lnRow = 2 For Each wsSheet In wbBook. Worksheets If wsSheet. Name - wsActive. Name Then wsSheet. Activate With wsActive . Hyperlinks. Add. Cells(lnRow, 1,, _ SubAddress: =' & wsSheet. Name & '! A1, _ TextToDisplay: =wsSheet. Name End With lnRow = lnRow + 1 End If Next wsSheet wsActive. Activate wsActive. Columns(A: B. EntireColumn. AutoFit With Application . DisplayAlerts = True . ScreenUpdating = True End With End Sub To use Visual Basic Macros, you will need to save the file as an Excel Macro Enabled Workbook. Click Macros under the Developer tab. Select the Create_ToC Macro and Run it. If your original worksheet is named differently, you may need to change all instances in the VBA script of ToC. A table of contents of all the worksheets should be created
Date: 2023-07-08

← How to Create a Pivot Chart in Excel 2016

How to Build an Amortization Table in Excel 2016 →

Related videos

Der8auer Has Gone Mad with Power - Heatsink Manufacturing - Made in Germany

• Gamers Nexus

Corsair, Get It Together

• Gamers Nexus

Intel's New GPU: Xe3 Architecture Changes, Handheld Gaming CPUs, & XeSS3

• Gamers Nexus

Photoshop's New Blemish Removal is GENIUS: Explained

• PiXimperfect

AM4 Lives: AMD Ryzen 5 5500X3D CPU Review & Benchmarks

• Gamers Nexus

NEW Photoshop Feature: Auto-Rename Messy Layers!

• PiXimperfect

Comments and reviews: 4

Anita
Is there a way for excel to automatically add the new sheet I created to the table of contents? Or do I have to repeat this step every time there is a new sheet?
reply

NJCLoud
I'm running into an issue getting a run-time error '13'. It highlights ' If wsSheet. Name - wsActive. Name Then ' in VBA. Followed everything else exactly.
reply

Dan
This is awesome! Thank you so much! I just need to create a link that takes me back to the TOC so that I'm not having to scroll through all the worksheets. :)
reply

Chomp
What would I need to do to this code to add a page numbers column and put the TOC in the second tab (so I can have the Cover Page before it?
Thanks!
reply

Add a review, comment