![]() |
#1
|
|||
|
|||
![]()
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? Last edited by cooloox; 03-27-2017 at 05:20 AM. Reason: To make my post more understandable |
Tags |
hyperlinks, toc |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
Reports Filter Generation | ebilbrough | Project | 3 | 01-05-2016 06:13 AM |
![]() |
copwriter | Excel | 3 | 06-28-2014 05:59 PM |
![]() |
mrphilk | Excel | 2 | 06-08-2010 12:39 PM |
How to reduce top margin in even and odd pages in pdf generation using msword | Nandu4u | Word | 0 | 11-20-2009 07:16 AM |
Templates: automatic text generation from Rich Text content control | Chickenmunga | Word | 0 | 10-01-2008 11:16 AM |