I am trying to code my master workbook to open 6 different workbooks and copy the range on the first worksheet of each of the six sheets and copy the columns of data across sheet 1 of my master workbook. My first error says it can not find the first filename in my directory I am copying from.
And will this code put the data in the next empty column on the master workbook. Please tell me what I am doing wrong with my code. Thank you very much. Ken Mc

Sub LoopThroughDirectory()
Dim MyFile As String
Dim ecolumn
MyFile = Dir("C:\Users\Ken\Desktop\Charlie's Project\")

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

Workbooks.Open (MyFile)
Range("B1:U69").Copy

ecolumn = Sheet1.Cells(Column, 1).End(xlRight).Offset(1, 0).Column
ActiveSheet.Paste Destination:=Worksheets("sheet1").Range(Cells(ecolumn, 1), Cells(ecolumn, 14))
MyFile = Dir

Loop
End Sub