- Got Sheet
- Pages
- Worksheet Directory VBA
Generate a Worksheet Directory with VBA
Hey there!
Sign up below and I will email you the Excel workbook with VBA code shortly.
You’ll also get several more free resources over the coming days that I hope you find helpful!
If you don’t like free stuff and just want the VBA code, here it is 👇
Sub CreateSheetDirectory()
Dim ws As Worksheet
Dim directorySheet As Worksheet
Dim row As Long
Dim exists As Boolean
'Check if Directory sheet exists
exists = False
For Each ws In ThisWorkbook.Worksheets
If ws.Name = "Directory" Then
exists = True
Set directorySheet = ws
Exit For
End If
Next ws
'Create Directory sheet if it doesn't exist
If Not exists Then
Set directorySheet = ThisWorkbook.Worksheets.Add(Before:=ThisWorkbook.Worksheets(1))
directorySheet.Name = "Directory"
End If
'Clear existing content
directorySheet.Cells.Clear
'Add header
With directorySheet
.Range("A1").Value = "Sheet Name"
.Range("B1").Value = "Go To Sheet"
.Range("A1:B1").Font.Bold = True
End With
'Add sheet names and hyperlinks
row = 2
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Directory" Then
With directorySheet
.Cells(row, 1).Value = ws.Name
.Hyperlinks.Add Anchor:=.Cells(row, 2), _
Address:="", _
SubAddress:="'" & ws.Name & "'!A1", _
TextToDisplay:="Click to Go"
End With
row = row + 1
End If
Next ws
'Format the directory
With directorySheet
.Columns("A:B").AutoFit
.Range("A1:B" & row - 1).Borders.LineStyle = xlContinuous
.Range("A1:B1").Interior.ColorIndex = 15
End With
'Activate Directory sheet
directorySheet.Activate
MsgBox "Sheet Directory has been created!", vbInformation
End Sub
