Hyperlink Help


S

Sandy

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
 
Ad

Advertisements

E

excelent

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:
 
J

JLatham

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
 
S

Sandy

This works great ---- thanks

JLatham said:
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
 
Ad

Advertisements

J

JLatham

Glad I could help.

Enjoy!

Sandy said:
This works great ---- thanks

JLatham said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top