I need to use fill-handle with hyperlinks

[Kevin McCann]

I would like to use something like the fill handle in Excel to help
fill in a spreadsheet with hyperlinks. I want to link:

Sheet1 A1 to Sheet2 A1
Sheet1 A2 to Sheet2 A2
Sheet1 A3 to Sheet2 A3

....and so on. So far I have not had much luck. I can get it done one
cell at a time, but I would like to do about 1000 of them and there is
no way I am going to spend that much time on this. Does anyone know of
a way to do this automatically? Thanks in advance!

-Kevin

 

[Doug Glancy]

Kevin,

If you use the Hyperlink function, you can then use the fill handle. In
Sheet1A1 put this formula:

=HYPERLINK(Sheet2!A1)

and then copy down. Using this function is said to be more robust than
regular hyperlinks anyways.

hth,

Doug

 

[Doug Glancy]

Kevin,

Well, my first reply was useless I think. Perhaps a macro like this?:

Sub AddHyperlinks()
Dim i As Long

For i = 1 To 1000
Worksheets("Sheet1").Hyperlinks.Add _
Anchor:=Worksheets("Sheet1").Range("A" & i), _
Address:="", _
SubAddress:="Sheet2!A" & i, _
TextToDisplay:="Sheet2!A" & i
Next i
End Sub

hth,

Doug

 

[Kevin McCann]

Kevin,

If you use the Hyperlink function, you can then use the fill handle. In
Sheet1A1 put this formula:

=HYPERLINK(Sheet2!A1)

and then copy down. Using this function is said to be more robust than
regular hyperlinks anyways.

hth,

Doug

Hey Doug

Thanks for the response. I don't think I was clear enough with what I
am trying to do though. When I click the hyperlink in Sheet1 A1, I
want to be taken to Sheet2 A1. When I do what you suggested, the
hyperlink shows the text that is in Sheet2 A1 and when I click it, I
get an error that says "Cannot open the specified file." Any other
suggestions? Thanks!

 

[Kevin McCann]

Kevin,

Well, my first reply was useless I think. Perhaps a macro like this?:

Sub AddHyperlinks()
Dim i As Long

For i = 1 To 1000
Worksheets("Sheet1").Hyperlinks.Add _
Anchor:=Worksheets("Sheet1").Range("A" & i), _
Address:="", _
SubAddress:="Sheet2!A" & i, _
TextToDisplay:="Sheet2!A" & i
Next i
End Sub

hth,

Doug

Haha. You beat me to a reply. I will give it a try. Thanks!

 

[Kevin McCann]

That worked perfectly! Thank you very much for the help!

 

[Doug Glancy]

Kevin,

Second time's the charm . Glad I could help.

Doug