How can i get my hyperlink to work? (Excel, VBA)

Question

I have a problem with my hyperlink in excel. Im trying to set a hyperlink from one sheet to another but the source and target cell needs to change every time in the loop. Basically i want to move data from one cell in GROUP 1 to another cell in GROUP 2 then set a hyperlink from GROUP 2 back to the same cell in GROUP 1. I have like 200 values so i want to do this in a loop. I just can´t figure out whats wrong whit my SubAddress!!

This is my code below.....

Thank you for any help.

Sub Transfer_and link()
Dim i As Integer
Dim LastRow1, As Long

LastRow1 = Sheets("GROUP 1").Cells(Rows.Count, "A").End(xlUp).Row

a = 14
For i = 5 To LastRow1 Step 2
Sheets("GROUP 2").Cells(a, 2) = Sheets("GROUP 1").Cells(i, 1)
Sheets("GROUP 2").Cells(a, 3) = Sheets("GROUP 1").Cells(i, 9)
Sheets("GROUP 2").Cells(a, 4) = Sheets("GROUP 1").Cells(i, 10)

Sheets("GROUP 1").Activate
Cells(i, 1).Select
Worksheets(2).Hyperlinks.Add Anchor:=Worksheets(2).Cells(a, 2), Address:="", _
SubAddress:=ActiveCell.Address

a = a + 1
Next i

If i´m pressing on the hyperlink in GROUP 2 cell B14 it´s refers to Cell A5 in the same sheet (GROUP 2). I need it to refer to cell A5 in the other sheet (GROUP 1). — user2304163
– user2304163, Commented May 17, 2013 at 17:31

Daniel · Accepted Answer · 2013-05-17 17:15:32Z

4

ActiveCell.Address only returns the cell reference. You need to add the sheet reference too.

It'd be something like:

SubAddress:=ActiveCell.Worksheet.Name & "!" & ActiveCell.Address

If your sheet name may have a space like yours, it'd actually have to be this:

SubAddress:="'" & ActiveCell.Worksheet.Name & "'!" & ActiveCell.Address

answered May 17, 2013 at 17:15

Daniel

13.1k3 gold badges39 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2304163 Over a year ago

THANK U! I have been struggling all day with this and looking everywhere, problem is now solved :)

Jon Crowell Over a year ago

In that case, accept this answer. Based on your enthusiasm, you should probably upvote it as well.

Collectives™ on Stack Overflow

How can i get my hyperlink to work? (Excel, VBA)

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related