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"