VBA Code - Run-time Error 1004 can't find file

Hello,

I am VERY new to VBA and I am getting a strange error when I run my macro. The code is below and the goal is to combine "Sheet1" of several workbooks into a new workbook (basically making a master for all of my spreadsheets). When I run it, I get the error pictured below, however, the file it cannot find is definitely in the folder. Can anyone help me solve this error? THANK YOU!

Capture.PNG

CODE:

Sub LoopThroughDirectory()
Dim MyFile As String
Dim erow
MyFile = Dir("C:\CE Sign In\")

Do While Len(MyFile) > 0
If MyFile = "zCE Class Sign In MASTER 2018.xlsm" Then
Exit Sub
End If

Workbooks.Open (MyFile)
Range("A2:O4").Copy
ActiveWorkbook.Close

erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 15))

MyFile = Dir

Loop

End Sub

Try:

Option Explicit

Sub LoopThroughDirectory()
' for testing
Const sSourceFolder As String = "C:\Test\"
' for live work
'Const sSourceFolder As String = "C:\CE Sign In\"

Dim MyFile As String
Dim erow
MyFile = Dir(sSourceFolder)

Do While Len(MyFile) > 0
If MyFile = "zCE Class Sign In MASTER 2018.xlsm" Then
Exit Sub
End If

Workbooks.Open sSourceFolder & MyFile
Range("A2:O4").Copy
ActiveWorkbook.Close

erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Application.DisplayAlerts = False
ActiveSheet.Paste _
    Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 15))
Application.DisplayAlerts = True

MyFile = Dir

Loop

End Sub

What are the workbooks called?

Seems to work ok for me:

Option Explicit

' Issues.xlsm and Taxes.xlsm are the ones that work the macro.
' Green Energy.xlsm and Title.xlsm are the ones that don't.

Sub LoopThroughDirectory()
' for testing
Const sSourceFolder As String = "C:\Test\"
' for live work
'Const sSourceFolder As String = "C:\CE Sign In\"

Dim MyFile As String
Dim erow
MyFile = Dir(sSourceFolder)

Do While Len(MyFile) > 0
    Debug.Print "Read " & MyFile
    If MyFile = "zCE Class Sign In MASTER 2018.xlsm" Then
        Exit Sub
    End If

    Debug.Print "Process " & MyFile

    Workbooks.Open sSourceFolder & MyFile
    Range("A2:O4").Copy
    ActiveWorkbook.Close
    
    erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    Application.DisplayAlerts = False
    ActiveSheet.Paste _
        Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 15))
    Application.DisplayAlerts = True

    MyFile = Dir

Loop

End Sub

'Debug Output
'Read Green Energy.xlsm
'Process Green Energy.xlsm
'Read Issues.xlsm
'Process Issues.xlsm
'Read MonthEnd.xlsm
'Process MonthEnd.xlsm
'Read Taxes.xlsm
'Process Taxes.xlsm
'Read Title.xlsm
'Process Title.xlsm
'Read zCE Class Sign In MASTER 2018.xlsm

I guess I/we would need to see samples of all live files with any sensitive data anonymised. It may be the files are being processed but maybe overwriting previous data.