Hyperlink to Hidden Sheets

[Hatter172]

I'm trying to create a hyperlink in Excel to a sheet that is hidden. I have about 62 worksheets, uniquely named, and have all been hyperlinked to the main worksheet called "Main". I wonder if there's a way I can use the Main sheet to hyperlink sheets that are hidden.

Thanks,

 

[Jerry Sullivan]

Welcome to MrExcel,

Assuming that those are Hyperlink objects (not formulas that use the HYPERLINK worksheet function), then you could do that using VBA.

Paste this code into the Sheet Code Module of the sheet that has the hyperlinks.
(To access that module, Right-click on the sheet's tab > View Code)

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
 '--if hyperlink was unsuccessful at going to a valid range
 '  within this workbook, check if that range's worksheet is hidden
 '  and if so, unhide and try again to follow hyperlink.
 
 Dim rSubAddr As Range

 On Error GoTo ErrProc

 With Target
   '--check if hyperlink had valid subaddress
   If Len(.SubAddress) = 0 Then GoTo ExitProc
   
   '--validate hyperlink is within same workbook
   If Len(.Address) And UCase$(.Address) <> _
      UCase$(ThisWorkbook.Name) Then GoTo ExitProc
   
   On Error Resume Next
   Set rSubAddr = Me.Evaluate(Target.SubAddress)
   On Error GoTo ErrProc
   If rSubAddr Is Nothing Then GoTo ExitProc
   
   '--check if already successfully went to subaddress
   If rSubAddr.Address(External:=True) = _
      ActiveCell.Address(External:=True) Then GoTo ExitProc
   
   '--activecell is not subaddr, check if subaddr sheet hidden
   If rSubAddr.Parent.Visible <> xlSheetVisible Then
      '--unhide sheet
      Application.EnableEvents = False
      rSubAddr.Parent.Visible = xlSheetVisible
      '--try following hyperlink again
      Target.Follow
      Application.EnableEvents = True
   End If
      
 End With

ExitProc:
 Exit Sub

ErrProc:
 Application.EnableEvents = True
 MsgBox Err.Number & ": " & Err.Description
 Resume ExitProc
End Sub

This assumes the hyperlinks are within the same workbook. It could be extended to work with hyperlinks to ranges in other workbooks if that's needed.