macro for making hyperlinks

[Jack Sons]

Hi all,

Im my workbook in sheet 1 I have 100 names in A2:A101.
I want to make each name a hyperlink that leads to the cell with the
corresponding rownumber in column BB of sheet 2.
So clicking the name/hyperlink in A8 of sheet 1 leads to BB8 in sheet 2.
I can't figure out the code.
Your assistance will be appreciated.

Jack Sons
The Netherlands

 

[Guest]

Sub hyperlink()
For i = 2 To 100
Sheet1.Cells(i, 1).Activate
Name = Cells(i, 1)
SubAddress = "Sheet2!BB" & i
If Cells(i, 1) <> "" Then
ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 1), Address:="",
SubAddress:=SubAddress, TextToDisplay:=Name
End If
Next
End Sub

Press Alt+F11 and copy paste the above and try running the macro.

 

[squenson via OfficeKB.com]

I recorded a macro that creates such link and I simply add a loop around.
Here is the result:

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 02-08-2007 by SQ
'

For i = 1 To 65536
If Cells(i, "A").Value <> "" Then
Cells(i, "A").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
Address:="", SubAddress:= _
"Sheet2!BB" & i, TextToDisplay:=Cells(i, "A").Value
End If
Next i

End Sub