Table Of Contents for multi-tab workbooks

Hi all,

 

A quick and dirty way to create a table of contents for a multi-tab excel workbook. Run the attached macro and it will create a new tab, with the list of all existing tabs and a hyperlink to each of them.

Try it, you might like it.

Sub IndexAllTabs()
‘creates table of contents for workbook
Dim sht As Worksheet, i As Long
Dim shtDone As Worksheet

Set shtDone = ActiveWorkbook.Sheets.Add

On Error Resume Next
shtDone.Name = “TOC”

If Err.Number <> 0 Then
    ActiveWorkbook.Sheets(“TOC”).Delete
    shtDone.Name = “TOC”
    Err.Clear
End If
i = 1

For Each sht In ActiveWorkbook.Sheets
    If sht.Name <> shtDone.Name Then
        shtDone.Hyperlinks.Add Anchor:=ActiveSheet.Cells(i, 1), Address:=””, SubAddress:= _
            “‘” & sht.Name & “‘!A1”, TextToDisplay:=sht.Name
        i = i + 1
    End If
Next

End Sub

 

Thomas

Advertisements
This entry was posted in Macros.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s