Forum Discussion
Use VBA to Auto create a hyperlink in multiple cells for a column
what I have:
I have a workbook that has to 2 sheets. (Sheet1 and Sheet2).
Sheet1(ColumnA) has the column i need to hyperlink to cells(ColumnB) on sheet2.
What i need :
create a routine that will insert a hyperlink on Sheet1 relating to Sheet2.
example:
on Sheet1 have Cell A2 HYPERLINK TO B5 on Sheet2.
A3 will hyperlink to B6
A4 will hyperlink to B7 etc....
but hard code the range...so i can change the range if needed.
This formula sub seems to only do a single insert ?
Sub HyperlinkAnotherSheet()
Worksheets("Sheet1").Select Range("B5").Select ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'Sheet2'!A1"
End Sub
Thanks
fordraiders
staspe2475 If you can afford an extra column, this is easily solved using the HYPERLINK function:
=HYPERLINK("#'Sheet2'!"&CELL("address",B5),"link")
4 Replies
Sub HyperlinkAnotherSheet()
for i=5 to 10
Worksheets("Sheet1").Select Range("B" & i).Select
ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'Sheet2'!A" & cstr(i-4)
next
End Sub
- peiyezhu
I added an additional Integer to increase the Sheet2 value cell
Sub HyperlinkAnotherSheet2()
Dim j As Integer
j = 5
For i = 2 To 10
Worksheets("LOT").Select Range("A" & i).Select
ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'Sheet2'!B" & CStr(j)
j = j + 1
Next
End Sub
I will change the i and j Integers as i move along the Sheet because of the many breaks i have in the rows.
Thanks !
staspe2475 If you can afford an extra column, this is easily solved using the HYPERLINK function:
=HYPERLINK("#'Sheet2'!"&CELL("address",B5),"link")- It would be ok but, I need the "LOT" (ColumnA) to increment also..if i dragged the formula down.
Thanks for the reply..!!
