Hyperlinks.Follow error: Run-time error '9': Subscript out of range

Question

After I activate the hyperlink I get the subscript out of range at the sheet selection line

'Hyperlink aktivieren und Sheet Overview Results
Selection.Hyperlinks(1).Follow NewWindow:=True, AddHistory:=True
Worksheets("Overview Results").Select
AuswerteWb = ActiveWorkbook.Name
'ActiveWindow.Close

The thing is that I have a macro which should use a path of a file as hyperlink and select the sheet "overview Results" from the hyperlinked file.

But I get

Run-time error '9': Subscript out of range

This error can occur when you reference a sheet name that does not exist. Double check the sheet name and check for leading and trailing spaces. — braX, Jun 20 '17 at 12:19

score 2 · Answer 1 · edited Jun 20 '17 at 12:51

Why use Hyperlinks.Follow and not Workbooks.Open? If you are opening a new workbook using the hyperlink you will want to do something like this:

Dim OpenedFile as Workbook

' Skip any errors that would occur with a null link
On Error Resume Next
Set OpenedFile = Workbooks.Open(Selection.Value)
On Error GoTo 0

' Ensure that the file is set before operating on it
If Not OpenedFile Is Nothing Then
    Dim TargetWorksheet as Worksheet

    On Error Resume Next
    Set TargetWorksheet = OpenedFile.Worksheets("Overview Results")
    On Error GoTo 0

    ' We use the same Nothing check before operating on the worksheet
    If Not TargetWorksheet Is Nothing Then
        TargetWorksheet.Activate
    End If
End If

AuswerteWb = OpenedFile.Name
'ActiveWindow.Close

I highly encourage you to learn about qualifying your statements (for example, Worksheets("") is an unqualified statement) since this will cause you many headaches. Similarly, avoid Selection, Select, Activate, ActiveWorkbook, etc.

Please dont forget to mark the answer as the correct answer :). I am glad it is working for you. — Brandon Barney, Jun 21 '17 at 15:28

Hyperlinks.Follow error: Run-time error '9': Subscript out of range

1 Answers1

Linked