Skip to content

Instantly share code, notes, and snippets.

Last active August 29, 2015 14:09
Show Gist options
  • Save RichPollock/fd2a4e6cc4a8b5f5f888 to your computer and use it in GitHub Desktop.
Save RichPollock/fd2a4e6cc4a8b5f5f888 to your computer and use it in GitHub Desktop.
Generate a hyperlinked table of contents linking to each sheet in an Excel workbook (blog post:
Sub GenerateLinkedTOCFromWorkSheetNames()
Dim ProposedTOCWorksheetName As String
Dim NewTOCWorksheetName As String
Dim CurrentWorksheet As Worksheet
Dim Count As Integer
ProposedTOCWorksheetName = "TOC"
NewTOCWorksheetName = "TOC"
RowCounter = 2
Application.ScreenUpdating = False
Do While SheetExists(NewTOCWorksheetName)
NewTOCWorksheetName = Application.InputBox( _
Prompt:="A sheet named '" & ProposedTOCWorksheetName & "' already exists. " & _
"Enter a new sheet name or type '" & ProposedTOCWorksheetName & "' to overwrite.", _
If NewTOCWorksheetName = ProposedTOCWorksheetName Then
Exit Do
End If
ProposedTOCWorksheetName = NewTOCWorksheetName
If SheetExists(NewTOCWorksheetName) Then
Sheets.Add Before:=Worksheets(1)
Worksheets(1).Name = NewTOCWorksheetName
End If
For Each CurrentWorksheet In Worksheets
If CurrentWorksheet.Name <> NewTOCWorksheetName Then
Sheets(NewTOCWorksheetName).Range("B" & RowCounter).Value = CurrentWorksheet.Name
Sheets(NewTOCWorksheetName).Hyperlinks.Add _
Anchor:=Sheets(NewTOCWorksheetName).Range("B" & RowCounter), _
Address:="", _
SubAddress:="'" & CurrentWorksheet.Name & "'!A1", _
TextToDisplay:=CurrentWorksheet.Name, _
RowCounter = RowCounter + 1
End If
Application.ScreenUpdating = True
End Sub
Function SheetExists(SheetName As String) As Boolean
Dim TestWorksheet As Worksheet
SheetExists = False
On Error Resume Next
Set TestWorksheet = Sheets(SheetName)
If Not TestWorksheet Is Nothing Then SheetExists = True
Set TestWorksheet = Nothing
On Error GoTo 0
End Function
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment