Welcome to the forum -- take a minute to look at the FAQs at the link in my sig.
As an aside, this thread was 6 years old and marked SOLVED so it might be better to start a new one rather than tag onto such an old one
Option Explicit
Sub Hyper()
Dim rHyperlink As Range, rDest As Range
Set rHyperlink = Worksheets("Sheet1").Cells(2, 2)
Set rDest = Worksheets("Sheet2").Cells(5, 5)
Call rHyperlink.Parent.Hyperlinks.Add(rHyperlink, vbNullString, "'" & rDest.Parent.Name & "'!" & rDest.Address, _
"Link to " & rDest.Address(False, False), "This goes to " & "'" & rDest.Parent.Name & "'!" & rDest.Address(False, False))
Set rHyperlink = Worksheets("Sheet1").Cells(6, 6)
Set rDest = Worksheets("Sheet3").Cells(15, 15)
Call rHyperlink.Parent.Hyperlinks.Add(rHyperlink, vbNullString, "'" & rDest.Parent.Name & "'!" & rDest.Address, _
"Link to " & rDest.Address(False, False), "This goes to " & "'" & rDest.Parent.Name & "'!" & rDest.Address(False, False))
End Sub