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?