Search Functions‎ > ‎

Hyperlink Sheets

PROBLEM:
"My workbook has a lot of sheets.  I need to create a quick INDEX sheet at the beginning
of my workbook to serve as a Table of Contents. Hyperlinks from the index to the sheets
would be a plus."

SPECIFICATIONS:
  1. All sheets are listed in the new index sheet by name
  2. Hyperlink is added to make navigation to sheets simple
  3. Hyperlinks work even with spaces in sheet names
  4. Demonstrates how to also grab some some key data from each sheet to make list more informative
  5. Hyperlink is also placed on each original sheet in cell A1 to link back to the new index, making a complete fast-navigation system

To create a hyperlinked list of all the worksheets in a workbook on the current active sheet in column A:
Sample File:     CreateHyperlinkedSheetList.xls

CODE

Option Explicit

Sub CreateHyperlinkedSheetList()
'Author:    Jerry Beaucaire
'Date:      1/3/2011
'           8/4/2012 added ability to collect data from
each sheet
Dim ws As Worksheet, wsLIST As Worksheet
Application.ScreenUpdating = False

Set wsLIST = Sheets.Add(Before:=Sheets(1))

For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> wsLIST.Name Then
        With wsLIST.Range("A" & Rows.Count).End(xlUp)
            .Offset(1).Value = ws.Name
            ActiveSheet.Hyperlinks.Add Anchor:=.Offset(1), Address:="", SubAddress:= _
                "'" & ws.Name & "'!A1", TextToDisplay:=ws.Name
            .Offset(1, 1).Value = ws.Range("C12").Value
            .Offset(1, 2).Value = ws.Range("C15").Value
            .Offset(1, 2).Value = ws.Range("C18").Value
        End With
        ws.Range("A1").Formula = "=HYPERLINK(""#'" & wsLIST.Name & "'!A1"",""Home"")"
    End If
Next ws
   
Application.ScreenUpdating = True
End Sub



Nothing says "thanks" like a steak dinner!
PayPal - The safer, easier way to pay online!

Comments