Creating an index sheet for a workbook

If you have a workbook with many pages you often would like to create an index sheet with hyperlinks to get to the various pages. This gets to be a pain to maintain, especially if you move sheets around or delete and add them. Here is how to automate the production of an index sheet. You’ll find the code for this, and a generated index sheet, in the cDataSet.xlsm workbook, downloadable from Downloads

Page Content hide
1 The code
2 Summary

The code

This is pretty straightforward, and just references a few of the utilities in the UsefulStuff Module. If the required index sheet is missing it will create it.

Private Sub create()
    createAnIndexPage
End Sub
' create an index page
Public Sub createAnIndexPage(Optional indexName As String = "indexSheet", _
                            Optional clearIndex As Boolean = False)
    Dim ws As Worksheet, w As Object, r As Range
    Set ws = sheetExists(indexName)
    If ws Is Nothing Then
        Set ws = Sheets.add
        ws.name = indexName
        ws.Cells(1, 1).Value = "Sheet"
    End If
    If clearIndex Then
        With wholeWs(ws)
            If .rows.count > 1 Then
                .Offset(1).ClearContents
            End If
            deleteAllFromCollection .Hyperlinks
        End With
    End If
    Set r = firstCell(ws.Cells)
    For Each w In Sheets
        If w.name <> ws.name Then
            If isSheet(w) Then
                Set r = r.Offset(1)
                ws.Hyperlinks.add r, "", SAd(w.Cells(1, 1)), , w.name
            End If
        End If
    Next w
End Sub

Summary

That’s all there is. For more tips like this see Get Started Snippets.
In the meantime why not join our forum, follow the blog or follow me on twitter to ensure you get updates when they are available.