Open hyperlink with macro in all sheets of a workbook

Question

Sub Macro4()
    Range("B1").Select
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
End Sub

I have one Excel workbook with three sheets (sheet 1, sheet 2 & sheet 3), in sheet1 in B1 cell I have hyperlink which I open with the recorded macro command, however when I run the macro in sheet no. 2 then command fails with error run-time error '9'.

Please help as I want to use this command in such a way that if I have opened sheet2 or sheet 3 then still it may open the hyperlink of sheet 1.

try ThisWorkbook.Worksheets("sheet1").Range("B1").Hyperlinks(1)... — Dmitry Pavliv

SilverShotBee SilverShotBee · Accepted Answer · 2014-06-28T20:04:18

Sub Macro4()
    Range("B1").Select
    Worksheets("sheet1").Range("B1").Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
End Sub

You need to tell VBA that the hyperlink is on another sheet if your referencing it from a different sheet. By adding Worksheets("Sheet1") your telling VBA to always look at Sheet1 regardless of what sheet is actually activated. The Range("B1") is simply narrowing the range in which a hyperlink can be found and deemed as the 1st hyperlink

Open hyperlink with macro in all sheets of a workbook

1 Answers