Complex Hyperlink Function

[cmeier7]

Hi, in VBA I have

Sheets("" & tab_name & "").Cells(1, 2) = "=HYPERLINK("""" & 'Sheet1'!A2 & ""'Sheet1'!A1"""",""To Main Roll-Up"")"

tab_name is a defined variable.
The code inserts an equation containing a hyperlink function. In 'Sheet1'!A2, I have the current workbook name (ie [Book1]). I believe there is an issue with all of the quotations. Can anyone figure out this puzzle? I've been at it for a while with no luck.

Oh, btw, I get error message '1004' - Application-defined or object-defined error

 

[cmeier7]

Ok, for clarification purposes, I want VBA to put this formula (below) into the specific cell. The below formula works. I have the workbook name in Cell A2 of Sheet1. I am wanting to place the hyperlink into Cell C1 of " & tab_name & " which is a String defined variable and changes with every loop in my code.

Sheets(tab_name).Cells(1,3)=HYPERLINK("" & 'Sheet1'!A2 & "'Sheet2'!A1", "To Reachback")

 

[cmeier7]

I'm sorry, the forum froze for me before I could edit my post and formula. Disreguard the previous 2 posts.
_______________________________________________________________________________________


Ok, for clarification purposes, I want VBA to put this formula (below) into the specific cell. The below formula works. I have the workbook name in Cell A2 of Sheet1. I am wanting to place the hyperlink into Cell C1 of " & tab_name & " which is a String defined variable and changes with every loop in my code.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Formula in Cell C1-
<o></o>
<o></o>
=HYPERLINK("" & 'Sheet1'!A2 & "'Sheet2'!A1", "To Reachback")<o></o>