The problem is that the "hide" functionality was DESIGNED to make the sheet
inacessible
without removing it.
At least temporarily.
The hyperlink  REQUIRES an accessible location.
You might be able to use the hyperlink to link to some other object that could
then, in turn, run the VBA, but I think this works "cleaner":
Here's what I did:
I have a spreadsheet that has a tab called "Summary", and a whole bunch of data
tabs.
In a module, I added the macros:
'==============================
Option Explicit
Dim I
Sub Refresh_List()
Dim inx
Range("C3:C65000").ClearContents
inx = 2
For I = 1 To Sheets.Count
If (UCase(Sheets(I).Name) <> "SUMMARY") Then
inx = inx + 1
Cells(inx, 3) = Sheets(I).Name
End If
Next I
End Sub
Sub Hide_Sheets()
Application.ScreenUpdating = False
For I = 1 To Sheets.Count
If (UCase(Sheets(I).Name) <> "SUMMARY") Then
Sheets(I).Visible = False
End If
Next I
Application.ScreenUpdating = True
End Sub
' (for Debugging purposes only)
Sub Unhide_Sheets()
Application.ScreenUpdating = False
For I = 1 To Sheets.Count
Sheets(I).Visible = True
Next I
Application.ScreenUpdating = True
End Sub

'==============================
In the VBA for the "Summary" sheet, I added:
'==============================
Private Sub Worksheet_Activate()
Hide_Sheets
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If (Target.Value <> "") And (Not Intersect(Range(Target.Address),
Range("C3:C65000")) Is Nothing) Then
Application.ScreenUpdating = False
Sheets(Target.Value).Visible = True
Sheets(Target.Value).Select
Cancel = True
Application.ScreenUpdating = True
End If
End Sub
'==============================
If you run "Refresh_List", it will find all of the tab names and put them in a
list starting in cell C3.
Now, if you double-click on any of the tab names, it will unhide the tab and
select it.
When you select the "Summary" tab, it hides all the other sheets.
Seems to work pretty clean to me.
If you have formulas that you need to add to the Summary tab for each data tab,
you can have the macro insert the formula.