Create a Master Sheet with Hyperlinks

August 11th, 2007

Sometimes it's a good idea to have a master worksheet that would have hyperlinks to the other sheets in the workbook. Here's a sub procedure that would create a worksheet named "Index" and create the hyperlinks.

Sub CreateIndexSheet()
Dim ws As Worksheet
Dim wsIndex As Worksheet
Dim lRow As Long

With ActiveWorkbook
Set wsIndex = .Worksheets.Add(Before:=.Worksheets(1))
wsIndex.Name = "Index"
End With

With wsIndex.Range("A1")
.Value = "Index"
.Font.Bold = True
End With

lRow = 2
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Index" Then
With wsIndex
.Range("A" & lRow).Value = ws.Name
.Hyperlinks.Add Anchor:=.Range("A" & lRow), Address:="", SubAddress:=ws.Name & "!A1"
lRow = lRow + 1
End With
End If
Next ws
wsIndex.Columns("A").AutoFit
End Sub

Posted in Excel, How To, VBA | No Comments

No Comments yet »

RSS feed for comments on this post. TrackBack URI

Vergel’s Excel and Visual Basic Blog