Copying Hyperlinks in Excel VBA

Terry_Orange

Board Regular
Joined
Jul 22, 2002
Messages
133
Hi folks,

I have an excel sheet with thousands of hyperlinks.

In a seperate sheet I have a list of some of these websites named, which I VLOOKUP and find. I then want to copy across the actual hyperlink (and print out the text for a user to see).

YAHOO (www.yahoo.com)

I can find it fine, but I can't access the hyperlink information to copy it across. I can add a hyperlink - but I don't know how to assess the original...

activecell.hyperlink. ????

Any ideas anyone !?!?!?!?!

THANK YOU AS ALWAYS.
:unsure:

TO.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Andrew.

Thanks for that - it copies across the link (which I've managed - though a long piece of macro code!), but....

is there any (easy) way to get the actual weblink address....

eg. the www.yahoo.com bit as a non clickable..... ?

as usual we are here to challenge you!

cheers,
TO.
 
Upvote 0
and actually the link doesn't work when clicked anyway....

o_O

hmmm......

It looks right! ;) but just comes up with a msg CAN'T FIND SPEcIFICIED FILE !

TO.
 
Upvote 0
Re: Copying Hyperlinks in Excel VBA **RESOLVED**

in VBA I can use:

Dim HyperLink1 as String
HyperLink1 = ActiveCell.Hyperlinks(1).Name

and then simply put this into my cell.

Thanks for your help Andrew. As ever very gratefully appreciated.

TO.
 
Upvote 0
I had on Sheet1:
BOOK7
ABCD
1Ahttp://216.92.17.166/
2Bhttp://216.92.17.166/board2/
Sheet1


and on Sheet2:
BOOK7
ABCD
1Ahttp://216.92.17.166/
Sheet2


Click the Hyperlink that results from the formula:

=HYPERLINK(VLOOKUP(A1,Sheet1!$A$1:$B$2,2,FALSE))

worked for me.
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top