Sub or function error

Question

I am new to vba, I want to transfer data from multiple sheets in one folder to one sheet. I wrote the programme as follows:

Sub LoopThroughDirectory()

Dim MyFile As String
Dim erow
MyFile = Dir("C:\Bulletinwork\")

Do While Len(MyFile) > 0
If MyFile = "Bmaster.xlsm" Then
Exit Sub
End If

Workbooks.Open (MyFile)
Range("A4:I42").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, 9))

MyFile = Dir

Loop

End Sub

Can someone help me find the reason why when I try to run the programme, I get an error message saying "sub or function not defined".

Kenny

Answer 1

The following line will be causing some grief:

Workbooks.Open (MyFile)

The () means that VBA is trying to evaluate MyFile before running the Open command. Of course, MyFile is a string/path so can't run.

Try

Workbooks.Open MyFile

instead.