David has attended:
Excel VBA Intro Intermediate course
Create a contents page
Hi there,
I'd like to have a macro which sits in my personal macro workbook that allows me to create a contents page for any excel workbook I access.
The contents page should include:
- a list of all the individual worksheets
- each worksheet name should also be a hyperlink to cell a1 of the corresponding sheet
It would also be handy to have the ability to
-name the contents page if a worksheet titled 'contents' already exists; and
- subsequently update the contents of the 'contents' worksheet I create.
Thanks!
Create a contents page Using A Loop
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
