With Hyperlink formula & vba, I want to copy text of hyperlink to the cell it's linked to

Hello!


Ok, so I've got this formula: =HYPERLINK("#'Sheet1'!D"&ROW(H11),H11)

What it does is create a hyper link (in B11) and pull the text for said hyperlink from H11, and links to D11. As I copy the formula down, it pulls the text from the next cell down in column H, and links to the next cell down in column D. So the next cell down (B12) for example will pull its text from H12 and link to D12.

The next thing I need this to do is when you click on the hyperlink in say, B11, I need the text of that hyperlink to copy and paste to the cell it's linked to (D11).

I've got this code, which works, but it causes issues:

Please Login or Register  to view this content.

Here's what it looks like: http://imgur.com/qoxSocl

The issue is if you highlight any group of cells anywhere I get "Run-time error '13': Type mismatch. The more important issue is when the code is entered in VBA, anytime any of the cells with a hyperlink formula are selected, the formula disappears. So I basically just need the code to not cause these issues...

Thanks so much!

thanks! This works perfect.