Create Table of Contents on Excel
#excel #spreadsheets #workbooks #shortcuts #formulas #exceltips #exceltricks #learnexcel
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.
Remove Duplicates #excel.with.layael #excel #spreadsheets #workbooks #shortcuts #formulas #exceltips #exceltricks #learnexcel
Proper Text #excel.with.layael #excel #spreadsheets #workbooks #shortcuts #formulas #exceltips #exceltricks #learnexcel
Autosum Multiple Columns&Rows
#excel.with.layael #excel #spreadsheets #workbooks #shortcuts #formulas #exceltips #exceltricks #learnexcel
Creating Sparklines
#excel.with.layael #excel #spreadsheets #workbooks #shortcuts #formulas #exceltips #exceltricks #learnexcel
Inserting Dividers
#excel.with.layael #excel #spreadsheets #workbooks #shortcuts #formulas #exceltips #exceltricks #learnexcel
Screenshot Workbook to new Workbook #excel.with.layael #excel #spreadsheets #workbooks #shortcuts #formulas #exceltips #exceltricks #learnexcel
Import PDF into Excel #excel.with.layael #excel #spreadsheets #workbooks #shortcuts #formulas #exceltips #exceltricks #learnexcel
Lower/Upper/Proper Name Changes #excel.with.layael #Excel #spreadsheets #workbooks #shortcuts #formulas #exceltips #exceltricks #learnexcel
Remove Blanks - Columns & Rows #excel.with.layael #excel #spreadsheets #workbooks #shortcuts #formulas #exceltips #exceltricks #learnexcel
Create Barcodes #excel.with.layael #excel #spreadsheets #workbooks #shortcuts #formulas #exceltips #exceltricks #learnexcel
XLOOKUP - Using 3 Criterias
#excel.with.layael #excel #spreadsheets #workbooks #shortcuts #formulas #exceltips #exceltricks #learnexcel
XLOOKUP Using 1 Criteria
#excel.with.layael #excel #spreadsheets #workbooks #shortcuts #formulas #exceltips #exceltricks #learnexcel
Separate Date & Time into Separate Cells
#excel.with.layael #excel #spreadsheets #workbooks #shortcuts #formulas #exceltips #exceltricks #learnexcel
Number Sequences
#excel.with.layael #excel #spreadsheets #workbooks #shortcuts #formulas #exceltips #exceltricks #learnexcel
Fixing Data Cells
#excel.with.layael #excel #spreadsheets #workbooks #shortcuts #formulas #exceltips #exceltricks #learnexcel
Create Drop-down Lists
#excel.with.layael #excel #spreadsheets #workbooks #shortcuts #formulas #exceltips #exceltricks #learnexcel
Date Sequence
#excel.with.layael #excel #spreadsheets #workbooks #shortcuts #formulas #exceltips #exceltricks #learnexcel
Data Analysis
#excel.with.layael #excel #spreadsheets #workbooks #shortcuts #formulas #exceltips #exceltricks #learnexcel
Conversions - From Meter to Milimeter
#excel.with.layael #excel #spreadsheets #workbooks #shortcuts #formulas #exceltips #exceltricks #learnexcel
