If you still want to go with the VBA solution, try this one. To put it into
your workbook, press [Alt]+[F11] to open the VB Editor, choose Insert |
Module and cut and paste the code below into the module. Change the sheet
name and "home cell" values as needed for your workbook. Then use Tools |
Macro | Macros to run the macro.
Sub MakeTOC()
Dim tocWS As Worksheet
Dim tocHome As Range
Dim anyWS As Worksheet
Dim mySubAddress As String
Dim homeCellAddress As String
Dim tocEntryCount As Integer
'change these as needed
'provide name of sheet to have the Table of Contents
Set tocWS = Worksheets("Sheet1")
'change to whatever cell you'd like to go to on a sheet
'via the hyperlink
homeCellAddress = "A1"
'this all assumes that the TOC sheet is now empty
tocWS.Activate
Set tocHome = tocWS.Range("A1")
Application.ScreenUpdating = False ' speed things up
For Each anyWS In Worksheets
If anyWS.Name <> tocWS.Name Then
'add it to the Table of Contents list
mySubAddress = "'" & anyWS.Name & "'!" & _
homeCellAddress
'need to add hyperlink also
tocHome.Offset(tocEntryCount, 0).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
Address:="", SubAddress:= _
mySubAddress, TextToDisplay:=anyWS.Name
tocEntryCount = tocEntryCount + 1
End If
Next
End Sub
Sandy said:
I have a workbook with 100 worksheets. The first worksheet contains a list of
all the worksheet names in the workbook. How can I convert this list to
hyperlinks to each sheet within a vba macro.
I have tried the hyperlink finction hyperlink(cell reference) but it results
in an invalid hyperlink.
Thank you
