Solved Create macro to hyperlink between 2 worksheets

April 10, 2014 at 20:53:10
Specs: Windows 7
1st cell A4 (worksheet: SumList) -- link to cell K1 (worksheet: Communication History)
2nd cell A5 (worksheet: SumList) -- link to cell K27 (worksheet: Communication History)
3rd cell A6 (worksheet: SumList) -- link to cell K53 (worksheet: Communication History)
This goes on until A3004 (3,000 records) -- each time link to cell in worksheet: Communication History with an incremental of 26 cells.

Please advise how I could use macro to create hyperlink to another worksheet with an incremental of 26 cells for 3,000 records.

Thank you.


See More: Create macro to hyperlink between 2 worksheets

Report •


#1
April 11, 2014 at 09:25:44
✔ Best Answer
I believe that this code does what you ask, but I had to change Communication History to Communication_History.

It appears that building the Hyperlink in VBA doesn't work correctly if there is a space in the sheet name. It throws up a Reference Is Not Valid error when you try to use the Hyperlink.

Sub IncrementHyperlink()
'Set Row For First Link
   linkRw = 1
'Loop Through Rows 4 - 3004
  For rw = 4 To 3004
'Build Hyperlink By Appending Link Row Variable, Place In Column A
     Sheets("SumList").Hyperlinks.Add _
          Anchor:=Cells(rw, 1), Address:="", _
          SubAddress:="Communication_History!K" & linkRw, _
          TextToDisplay:="Communication_History!K" & linkRw
'Increment Link Row By 26
        linkRw = linkRw + 26
  Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#2
April 17, 2014 at 18:09:49
SOLVED!!!! The code is perfect!!!!

Appreciate your help!


THANK YOU!!


Report •

#3
April 17, 2014 at 19:31:15
I am trying to change your VBA code so that it will work on the following scenario (but somehow it doesn't work.);-

New scenario:
1st cell BD4 (worksheet: SumList) -- Link to cell B17 (worksheet: Communication_History)
2nd cell BD5 -- B43
Same increment of 26 cells.

Can you show me how to modify your original code below to work with the new scenario?

[Your original code]
Sub IncrementHyperlink()
'Set Row For First Link
linkRw = 1
'Loop Through Rows 4 - 3004
For rw = 4 To 3004
'Build Hyperlink By Appending Link Row Variable, Place In Column A
Sheets("SumList").Hyperlinks.Add _
Anchor:=Cells(rw, 1), Address:="", _
SubAddress:="Communication_History!K" & linkRw, _
TextToDisplay:="Communication_History!K" & linkRw
'Increment Link Row By 26
linkRw = linkRw + 26
Next
End Sub

Thank you, again!!!


Report •

Related Solutions


Ask Question