Dynamic Hyperlink to a hidden worksheet.

[Tanduay]

The VBA Follow hyperlink works only if I use "Ctrl+K" hyperlink,

however it does not work on dynamic hyperlink using =hyperlink(#'Sheet!'A1,"FriendlyName")

Thank you

 

[Chihiro]

Your formula syntax is off. It should be...
=HYPERLINK("#'Sheet Name'!A1","FriendlyName")

Also, Excel does not treat hyperlink created with HYPERLINK() formula like manually added hyperlink and does not add it to collection. Therefore you can't use VBA follow hyperlink directly on formula generated hyperlink.

To go to HYPERLINK formula destination.

You can split the cell formula using double quote as delimiter to extract destination, and use code to add hyperlink to collection. Something like below (assuming Cell B2, in active sheet contains HYPERLINK formula).

Sub Test()

x = Split([B2].Formula, """")

[B2].Hyperlinks.Add anchor:=[B2], Address:="", SubAddress:=x(1)
[B2].Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
End Sub

 

[Tanduay]

Thank you Chihiro for the prompt reply. Ü