vba courses london - Create a contents page

A simple way of doing this is on startup run a procedure that does the following:

1. Creates a new Spreadsheet Call it "Contents Page". You need to check if "Contents Page" already exists and delete it

2. Does a loop that checks each sheet in the book

3. Inside this loop create another loop that using a row counter pastes the name of the sheet in a cell on the "Contents Page" and Hyperlinks it to the named sheet as seen in the routine below:

Sub ContentSheet

Dim vSheet As Worksheet
Dim RowNum As Integer

RowNum = 2

Sheets.Add Before:=Sheets(1)
ActiveSheet.Name = "Contents Page"

For Each vSheet In ActiveWorkbook.Worksheets

'You could test for "Contents Page" and ignore it here

Sheets("Contents Page").Select
Cells(RowNum, 1).Select
ActiveCell.Value = vSheet.Name

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
vSheet.Name & "!A1", TextToDisplay:=vSheet.Name

RowNum = RowNum + 1

Next vSheet

End Sub