I know how to add a formula to a worksheet that my macro is looping through

ThisWorkbook.Sheets("Sheet1").Range("A" & i).Formula = "=1 1"

and I know how to set up a Hyperlink Formula if I know the sheet name

=HYPERLINK("#Sheet2!A1","Sheet2")

but im having some trouble combining the two,

I have the Sheet name stored in a Variable (Sheet_Name) and im trying to set up the Hyperlink Formula to contain that (So instead of #Sheet2 I want it to use the Sheet_Name variable instead)

I cant seem to get the syntax to work

CodePudding user response：

.Formula = "=HYPERLINK(""#'" & sheet_Name & "'!A1"",""" & sheet_Name & """)"

If you need double qutoes within the result string, you need to double them. It's sometimes tricky to find out, what's the right number of quotes ...

Furthermore you should put single quotes around the sheetname variable - as you need them if there is e.g. a space in the name ...