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
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
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.