Here's an example from a product I've been working on:

   Dim LGUP As Worksheet

   Dim LEDSRow As Long
   Dim LGUPRow As Long

   Dim Contents As String

   ' some names of worksheet tabs
   const LEDSDataSheet = "LEDS_Format"
   const LargeUpTimesSheet = "Large_Uptimes"

   Set LGUP = Sheets(LargeUpTimesSheet)

   LEDSRow = 3
   LGUPRow = 5

   Contents = "=" & LEDSDataSheet & "!" & "$B$" & Mid(Str(LEDSRow),
2)

   With LGUP

       ' prepare a hyperlink directly to this large value
       .Hyperlinks.Add Anchor:=LGUP.Cells(LGUPRow, 3), _
                       Address:="", _
                       SubAddress:=Contents, _
                       TextToDisplay:="Go There"
   End With

It creates a hyperlink on one sheet to another sheet.  This code is
just cut out of a larger subroutine but all the important parts should
be here for a workable example.

I notice that, when it's done, the hyperlink has a full reference to
the destination sheet; it's full name and filesystem location.  I
imagine that this will work from one workbook to another.

Brian Herbert Withun