Vba excel - making index pages?

Question

Is it possible to write a VBA code that creates a page with all the hyperlinks to each of the sheets in an excel workbook?

For example if there are 20 sheets (all with very random names), I want to make a new worksheet with links to each of these 20 sheets. Or if there are 50 sheets, I want to make this index page with a click of a button. Is it possible? If so could someone please help me write it?

Answer 1

This creates a new sheet "Index Sheet" and creates a list of links, using the sheet titles as the text and linking to the "a1" cell on each sheet.

VBA Code:

X = 0

Sheets.Add

ActiveSheet.Name = "Index Sheet"

ShtCount = ActiveWorkbook.Sheets.Count

For Each ws In Worksheets

If ws.Name <> "Index Sheet" Then

Worksheets("Index Sheet").Range("A2").Offset(X, 0) = ws.Name

Worksheets("Index Sheet").Range("A2").Offset(X, 0).Select

Worksheets("Index Sheet").Range("A2").Offset(X, 0).Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _

        ws.Name & "!A1", TextToDisplay:=ws.Name

X = X + 1

End If

Next ws

End Sub