Auto Generation of Hyperlinks

[cooloox]

Hi everyone,



I am new to these forums. I am just a VBA dabbler and I am stuck at trying to figure out a macro.

I want to make a manual table of contents on a Table of Contents worksheet with hyperlinks to different headings on different worksheets in the file. To make this easier than manually producing each hyperlink I am trying to write a macro to assist me. I want to be able to select a cell and have my macro add the text of that cell to the Table of Contents worksheet and turn it into a hyperlink pointing to the cell I selected.

My code so far is ALMOST there! The only issue I am having is being able to capture both the worksheet and cell reference when producing the hyperlink. My code is setting the correct cell reference, but not the correct worksheet. Here is my code:

Sub createHyperlinks()
'
' Macro to create hyperlinks
'
'
Dim tocText As String ' variable to hold text to appear in table of contents
Dim sh As Worksheet ' holds name of worksheet to be linked to
Dim selectedCell As Range ' holds cell reference of cell I want to be linked to

Set sh = ActiveSheet
Set selectedCell = ActiveCell
tocText = ActiveCell.Text
Sheets("Table of Contents").Select

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=selectedCell.Address, TextToDisplay:=tocText

End Sub


Let's say cell A5 on Worksheet 2 has text in it = "Cover 1". I click on that cell and run the above code. "Cover 1" appears in the Table of Contents worksheet and is converted to a hyperlink and does point to cell A5, but it points to A5 on the "Table of Contents" worksheet, not A5 on the "Worksheet 2" worksheet.

Every possible way of referring to Worksheet 2 I could think of resulted in an error. Can anyone help me with this please?

[NoSparks]

I used the macro recorder to see how/where Excel uses the sheet name.
Give this a try...

Sub createHyperlinks_2()
'
' Macro to create hyperlinks
'
'
Dim tocText As String       ' variable to hold text to appear in table of contents
Dim sh As String            ' holds name of worksheet to be linked to
Dim selectedCell As Range   ' holds cell reference of cell I want to be linked to

sh = ActiveSheet.Name
Set selectedCell = ActiveCell
tocText = ActiveCell.Text
Sheets("Table of Contents").Select

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", _
        SubAddress:=sh & "!" & selectedCell.Address, TextToDisplay:=tocText

End Sub

[cooloox]

Hi NoSparks,

Wow!! Thank you very much!!! You were spot on and I must have pasted the code into Excel incorrectly the first time. I think it was to do with the wrapped line. The macro works flawlessly and does exactly what I need it to do. I am very grateful for your help as I would not have been able to work this out for myself. I have been messing with this for hours. I can't believe you figured it out so quickly.

Thanks again, your help was most appreciated, have a fantastic day!

[cooloox]

Is there something I need to do to mark this issue as solved? The first response from NoSparks fixed the issue beautifully.

[NoSparks]

Glad I could help.

Supposedly there is something under the Thread Tools drop down, upper right on page, for marking the thread solved.