VBA - make hyperlink display text equal to active cell contents

How do I make the display text for this hyperlink equal to the contents of the active cell, cell B50, rather than the text "B50"?

The rest of the formula works, it's just the display text I need to edit.

Range("B50").Select ActiveCell.FormulaR1C1 = "=HYPERLINK(""[wbk]strLastSheetName!C13"",""B50"")" 

Edit: Now the display text is working, but the hyperlink isn't working. I'm trying to have the hyperlink to go to cell C13 of the last sheet in the workbook. I have:

Function GetLastSheetName() As String Dim wbk As Excel.Workbook Set wbk = Excel.ActiveWorkbook GetLastSheetName = wbk.Worksheets(wbk.Worksheets.Count).Name End Function 

Then within the Sub:

Dim strLastSheetName As String strLastSheetName = GetLastSheetName() Range("B50").FormulaR1C1 = "=HYPERLINK([" & wbk " "]strLastSheetName!C13," & Range("B50").Value & ")" 

1 Answer

I figured it out--this works:

 Range("B" & Lastrow).Select ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _ "" & strLastSheetName & "!C13"