Hyperlinks between two cells

[aa2000]

Hi folks

I have two workbooks one which contains a list of experiments and the other which contains the experiment data.

I am trying to write a macro that will go through the list in the first book and the experiments in the second and create a hyperlink between a name and the corresponding experiment.

Now I can get the macro to match the two cells, but I am struggling to understand the add hyperlink function in VBA. How can I get it to create a hyperlink between 2 cells in different worksheets?

 

[aa2000]

Ok I've made some progress with this and I can get the macro to create hyperlinks between the two files if the two values match. However I cannot get it the hyperlink to open a specific sheet.

Heres the code

Sub Links()

Set Lrange = Sheet3.Range("A20:A50")
Set Lrange2 = Sheet4.Range("A1:A20")


For Each cell In Lrange

If cell.Value Like "Experiment*" Then
s = cell.Value
a = Trim(Mid(Replace(s, " ", Space(100)), 100, 100))

For Each rcell In Lrange2

If rcell.Value = a Then
Range(rcell.Address).Hyperlinks.Add Anchor:=Range(rcell.Address), Address:=ActiveWorkbook.Path + "/" + ActiveWorkbook.Name, SubAddress:=cell.Address, TextToDisplay:=Range(rcell.Address).Text
End If
Next rcell
End If
Next cell

End Sub

I know that the sheet name should go in the sub address section, but how can I actually reference the sheet name?

Cheers

 

[RichardMGreen]

The SubAddress section normally looks like this:-
SubAddress:= "Sheet3!A1"

You'll need to change it to your specific requirements.
Must admit, I've never used hyperlinks in this manner though.

 

[aa2000]

Thanks Richard

I think I can adapt that format to work for me. This is also my first time using hyperlinks like this, and whilst it does work till I add the sub address part, getting the hyperlink to open a specific sheet/cell is a little trickier. I'll let you know how it turns out!

Cheers

 

[RichardMGreen]

No problem.
Post on here if you run into any snags.

 

[aa2000]

Well I hit a snag (big surprise that...)

Anyway I have the current code:

Sub Links()

Set Lrange = ActiveSheet.Range("A20:A50")
Set Lrange2 = Sheet4.Range("A1:A20")


For Each cell In Lrange

If cell.Value Like "Experiment*" Then
s = cell.Value
a = Trim(Mid(Replace(s, " ", Space(100)), 100, 100))

For Each rcell In Lrange2

If rcell.Value = a Then
t = rcell.Value
u = rcell.Address

u = ThisWorkbook.Path + ThisWorkbook.Name + "-" + "Sheet4" + u
MsgBox u

Range(rcell.Address).Hyperlinks.Add Anchor:=u, Address:=ThisWorkbook.Path + "/" + ActiveWorkbook.Name, SubAddress:=ActiveSheet.Name + cell.Address, TextToDisplay:=t
End If

Next rcell
End If
Next cell

End Sub

Now a bit more explanation:
cell is in workbook Book14
rcell is in workbook Book2

When the code from my previous post is run it does not remember that rcell is in book 2 and puts the hyperlink in the equivalent address of the active sheet of book 14. So if rcell.Address = A5, then the hyperlink is in cell A5 of sheet1 of book14.

Now I'm trying to get the code place the hyperlink in rcell.Address of book2.
Any suggestions?

Cheers

 

[RichardMGreen]

How are using opening the other workbook (or grabbing it's name)?
I don't like using Activeworkbook as it has a chance of referring to the wrong one.

 

[aa2000]

I am not sure I understood your question properly, but the workbook to which this code belongs is selected by "ThisWorkbook", with ActivWorkbook being the active on in which the experiment data lies.

I've done it this way, because the workbook with the experiment data is created by another series of macros, so its easier to run a macro on that from another workbook.

Does that actually make sense? If not let me know and I'll try and explain it better. This project however has achieved its main goals, and this hyperlinks macro is just an option which would be nice, but is not mandatory, so I think I may leave it as it is for the time being.

Cheers