excel - VBA Hyperlink to tab

Question

I'm creating an index page as I have an excel doc with 100+ tabs.

On the index page I originally had the name of the tab matching the text that would link to it. I used this code:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim ShtName As String
    ShtName = Target.Name
    Sheets(ShtName).Visible = xlSheetVisible
    Sheets(ShtName).Select
End Sub

The target tab was originally hidden, this code would unhide it, then take me to it, then I have code on the target tab which hides it again when the tab is deactivated. It was working fine.

Now I have 2 pages per "heading", so eg I have the heading "Shop" in A1 (no hyperlink), then I have "Details" in B1 and "Transactions" in C1 each with a hyperlink. The tab name for the hyperlink for in the Details cell is D_Shop and in the Transaction cell its T_Shop. The same process goes on for many lines Eg:

A	B	C
Shop	Details	Transactions
Cinema	Details	Transactions

question from:https://stackoverflow.com/questions/65912240/vba-hyperlink-to-tab

Answer 1

In case anyone else finds this helpful, get the sub address and keep only that which is before the exclamation mark in the link SubAddress:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim ShtName As String
    ShtName = Left(Target.SubAddress, InStr(1, Target.SubAddress, "!") - 1)
    Sheets(ShtName).Visible = xlSheetVisible
    Sheets(ShtName).Select
End Sub