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

Give the Function Hyperlink another try like :

=HYPERLINK("[Workbook1.xls]"&OFFSET(A2,,,,)&"!A1","Jump to sheet : "&A2&"
Celles A1")

this one jump to the sheetname u have in cells A2
remember Workbook1 have to be saved at least 1 time !!


"Sandy" skrev:

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

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" wrote:

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

This works great ---- thanks

"JLatham" wrote:

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" wrote:

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

Glad I could help.

Enjoy!

"Sandy" wrote:

This works great ---- thanks

"JLatham" wrote:

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" wrote:

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