VBA insert Hyperlinks to another sheet

[Bearlord]

Hello =) I'm new to VBA

I want to make a worksheet with a list of some sheets (within the same workbook) that are hyperlinks to those sheets.

Example: I have sheet named "Summary" where I have a list of the rest of the sheets in the same workbook. The list shows the names of the worksheets and i want to add a link so that when i click on the sheet's name, it sends me to that sheet. There are also other cells with important information about that sheet next to the cell containing the sheet's name (and that should be a link). The other information is already there, working fine. My only problem is the hyperlink in the sheet's name.

I've tried doing this :

SUMARY.Cells(lastRow + 1, 1).FormulaR1C1= "=HYPERLINK(" & SheetName & "!A1" & "," & SheetName & ")"

And a multitude of combinations related with the quote marks. I just can't make it work.

SheetName, lastRow and SUMARY are all previously defined and working fine (finding the last row, and obtaining the sheet's names)

If I don't use variables it works perfectly

SUMMARY.Cells(lastRow + 1, 1).FormulaR1C1 = "=HYPERLINK(""Sheet1!A1"",""Sheet1"")"

So the problem, I believe, is related with the use of nested quotation marks... any ideas on how to use this?

Thanks in advance for any help =)

--
Bear

 

[GRTorres]

Hello Bearlord, you were definately on the right track and this should help you. Now also, I included the workbook name (which you will have to change), which allows you to jump between worksheets if needed to.

I placed Sheet2!A1 in cell A1 and I got this result in A2 (you can move the resulting hyperlink to destination of your choice.

Sub createhyper()

Set a = Sheets("Sheet1")

a.Range("A2").Formula = "=HYPERLINK(""[Creating Hyperlinks.xlsm]" & a.Range("A1") & """, ""Sheet1"")"

End Sub

 

[Bearlord]

GRTorres Thank you so much!

That's exactly what I was trying to do. I really mixed up with the quotation marks xD I was quite puzzled and couldn't figure it out. Again, thanks for your help ^^