Open hyperlink with macro in all sheets of a workbook
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.
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