Run-time Error 1004 - Failing to find workbooks (o

Question

I have very little experience on macro's, and I've been searching a long time online before I found it was needed to post my question. What I'm aiming at is filling one form with information obtained from other forms. I'll put the 'consolidation file' in one folder and let people upload their filled out questionnaire forms in a seperate folder, one level deeper.

The weird issue is, that sometimes it fills out the consolidation file perfectly, and sometimes it simply does not do anything. I get a runtime error 1004, saying that the file cannot be found. It actually mentions the exact name of the file, but then fails to 'find' it.

Any help on this would be greatly appreciated.

Kind regards,

Martijn

Sub LoopThroughDirectory()

    Sheets("Data").Select

    Application.ScreenUpdating = False

    Dim FileNames() As String
    Dim MyFile As String
    Dim erow

    MyFile = Dir(ActiveWorkbook.Path & "\Filled out Forms\")

    Do While Len(MyFile) > 0
        If MyFile = "Z. Master.xlsm" Then
            Sheets("Candidate selection").Select
            Exit Sub
        End If

        Workbooks.Open (MyFile)
        Worksheets("Hidden Sheet").Range("B3:I13").Copy
        ActiveWorkbook.Close

        erow = Sheet1.Cells(Rows.count, 1).End(xlUp).offset(1, 0).Row
        ActiveSheet.Paste Destination:=Worksheets("Data").Range(Cells(erow, 1), Cells(erow, 8))

        MyFile = Dir
    Loop

    Application.ScreenUpdating = True

End Sub

Answer 1

Try this MyFile = Dir(Path & Filename)