Hyperlinks.Follow error: Run-time error '9'

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

Answer 1

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.