If i understand you well...
Try this:
Option Explicit
Sub Test()
AddHyperlink ThisWorkbook.Worksheets(1), "A5", "BC2006", "Go!"
End Sub
Sub AddHyperlink(wsh As Worksheet, insertInto As String, saddress As String, text2display As String)
wsh.Hyperlinks.Add Anchor:=wsh.Range(insertInto), _
Address:="", _
SubAddress:=wsh.Name & "!" & wsh.Range(saddress).AddressLocal, _
ScreenTip:=text2display, _
TextToDisplay:=wsh.Range(saddress).AddressLocal(ReferenceStyle:=xlR1C1)
End Sub