VBA Challenge! How do i save the fidelity of a VBA Hyperlink?

[Born2dive00]

Hello everyone I would like to thank you all in advance for your kind help, I have a good working knowledge of excel, BUT I am rather new to VBA and I could really use some of your help.

So I was able to create a simple formula that looked at cells Sheet2 A8 and B8 to create a hyperlink in C8, using this formula. =IF(OR(A8,(B8)=" "),(""),HYPERLINK(B8,A8))
This does exactly what I want it to do by creating a hyperlink, simple enough.

Now using this VBA Code I was able to put the result C8 (my newly created hyperlink) into Sheet1 A1, all good so far, I got the text without a code in A1 (it is imperative that A1 is free from typed formulas, as it will go into a table, and the formula will cause a mismatch).

HOWEVER it put the value in A1 as a text, and lost the newly generated hyperlink.

How can I preserve the fidelity of the generated Hyperlink (NOT the code, only the hyperlink!) with the VBA code???


Thank you all for your help

 

[Born2dive00]

I can not seem to be able to upload the book, so here is the VBA Code I used to put the Sheet2 C8 hyperlink into the Sheet1 A1 Cell.

Sub Diver()
Dim s1 As Worksheet, s2 As Worksheet
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
s1.Range("A1") = s2.Range("C8")
End Sub

Please let me know

Thank you all

 

[FormR]

Hi, welcome to the forum!

What does your hyperlink link to? Is it a cell in the spreadsheet, or a web page, or something else?

Maybe you can give us an example of what is in cells A8 and B8 of sheet2?