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

Example Index Sheet




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 forumfollow the blog or follow me on twitter to ensure you get updates when they are available.


Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly

If you prefer Video style learning I also have two courses available. also published by O'Reilly.
Google Apps Script for Developers and Google Apps Script for Beginners.








Comments