Copy hyperlinks

[Jesper Kaas]

Hi

I have a sheet containing 2-300 hyperlinks to webpages in a row. The
row to the left of the row with hyperlinks contains an ID for each
link. This is a nice table, and I thought it would be a simple task to
use VLOOKUP to put the hyperlinks in to another table that uses the
same ID as in the first table with hyperlink. To give you an idea of
what I mean, please look at the example tables below:

Table 1 Table 2
ID1 Hyperlink1 ID3
ID2 Hyperlink2 ID1
ID3 Hyperlink3 ID5
..
..
..
IDn Hyperlinkn

Table 2 should then use VLOOKUP to fetch the hyperlinks from Table 1.
The problem is that VLOOKUP does not put in the links as links, only
the text that the links show. Example: Lets say a hyperlink shows the
text "New York Times", and by clicking on the link it takes you to
www.nytimes.com. VLOOKUP then simply puts the text "New York Times in
the cell where VLOOKUP is. Same thing happens when using Copy and
Paste from Visual Basic. However, doing a manual Copy and Paste in the
sheet actually copies the links as links.
So if someone knows a way to copy/lookup the links, that actually
results in clickable links at the destination, I will be gratefull.

Thanks a lot for any help!

 

[Claus Busch]

Hi Jesper,

Am Sun, 05 May 2013 14:11:08 +0200 schrieb Jesper Kaas:

I have a sheet containing 2-300 hyperlinks to webpages in a row. The
row to the left of the row with hyperlinks contains an ID for each
link. This is a nice table, and I thought it would be a simple task to
use VLOOKUP to put the hyperlinks in to another table that uses the
same ID as in the first table with hyperlink. To give you an idea of
what I mean, please look at the example tables below:

Table 1 Table 2
ID1 Hyperlink1 ID3
ID2 Hyperlink2 ID1
ID3 Hyperlink3 ID5

modify following code to suit:

Sub CopyHyperlinks()
Dim rngC As Range
Dim c As Range
Dim LRow As Long

With Sheets("Sheet2")
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For Each rngC In .Range("A1:A" & LRow)
Set c = Sheets("Sheet1").Range("A1:A300") _
.Find(rngC, LookIn:=xlValues)
If Not c Is Nothing Then
c.Offset(0, 1).Copy rngC.Offset(0, 1)
End If
Next
End With
End Sub


Regards
Claus Busch

 

[CellShocked]

Hi

I have a sheet containing 2-300 hyperlinks to webpages in a row. The
row to the left of the row with hyperlinks contains an ID for each
link. This is a nice table, and I thought it would be a simple task to
use VLOOKUP to put the hyperlinks in to another table that uses the
same ID as in the first table with hyperlink. To give you an idea of
what I mean, please look at the example tables below:

Table 1 Table 2
ID1 Hyperlink1 ID3
ID2 Hyperlink2 ID1
ID3 Hyperlink3 ID5
.
.
.
IDn Hyperlinkn

Table 2 should then use VLOOKUP to fetch the hyperlinks from Table 1.
The problem is that VLOOKUP does not put in the links as links, only
the text that the links show. Example: Lets say a hyperlink shows the
text "New York Times", and by clicking on the link it takes you to
www.nytimes.com. VLOOKUP then simply puts the text "New York Times in
the cell where VLOOKUP is. Same thing happens when using Copy and
Paste from Visual Basic. However, doing a manual Copy and Paste in the
sheet actually copies the links as links.
So if someone knows a way to copy/lookup the links, that actually
results in clickable links at the destination, I will be gratefull.

Thanks a lot for any help!

Take your "=VLOOKUP(yada,yada)" and put "=HYPERLINK(VLOOKUP(yada,yada))"

 

[Jesper Kaas]

Take your "=VLOOKUP(yada,yada)" and put "=HYPERLINK(VLOOKUP(yada,yada))"

That one works if the original hyperlink-cell contains a webaddress
like http://www.nytimes-no. Unfortunatelu it does not work if the
original cell contains a clickable text that actually is a hyperlink,
like "New York Times" in my example.

Thanks anyway. It brings me part of the way.

 

[CellShocked]

That one works if the original hyperlink-cell contains a webaddress
like http://www.nytimes-no. Unfortunatelu it does not work if the
original cell contains a clickable text that actually is a hyperlink,
like "New York Times" in my example.

Thanks anyway. It brings me part of the way.

Yes... My lookups did/do refer to actual existing hyperlink entries
within the referenced cell itself.

 

[Jesper Kaas]

Hi Jesper,

modify following code to suit:

Sub CopyHyperlinks()
Dim rngC As Range
Dim c As Range
Dim LRow As Long

With Sheets("Sheet2")
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For Each rngC In .Range("A1:A" & LRow)
Set c = Sheets("Sheet1").Range("A1:A300") _
.Find(rngC, LookIn:=xlValues)
If Not c Is Nothing Then
c.Offset(0, 1).Copy rngC.Offset(0, 1)
End If
Next
End With
End Sub


Regards
Claus Busch

Hi Claus

Thanks a lot! Your code does what I want done. I can't say that i
understand the code, but it is easy to modify it to do what I need to
do, and look at the rest as a "black box".

Best regards

 

[Claus Busch]

Hi Jesper,

Am Sun, 05 May 2013 19:01:43 +0200 schrieb Jesper Kaas:

I can't say that i
understand the code, but it is easy to modify it to do what I need to
do, and look at the rest as a "black box".

if you can modify the code you understand the code ;-)
Thank you for the feedback


Regards
Claus Busch