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!

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