Copying column from 200+ workbooks to a master file (already attempted!)

Hi,

I am new to VBA and looking to be able to automatically generate a master dataset by automatically copying a certain column from ~200 raw data workbooks. These will be accumulating over several weeks. For example, I may have 10 files in week 1, then 20, then 30, etc. I want to be able to drag and drop a new set of Excel data files into a folder with all existing ones, click a button, and have my master Excel updated with all data available to date.

I have attempted this with 3 workbooks and one master file. If it worked correctly, the data would appear in columns C-E in the master file. However, my code is not working. I am fairly certain at least one of the problems is the way I defined "ecolumn". I also know that my code may not work if any data file is alphabetically after "Master", so if anyone can suggest a fix to that as well, that would be helpful.

Additionally, I would also eventually like to transpose all of the data so that each data source is a row, if anyone also would be able to help with that.

I have pasted the module below, and attached the sample files. Could anyone help me with this? Thanks!

Sub LoopThroughDirectory()
Dim MyFile As String
Dim ecolumn
MyFile = Dir("C:\Test")
Do While Len(MyFile) > 0
If MyFile = "Master.xlsm" Then
Exit Sub
End If
Workbooks.Open (MyFile)
Range("C1:C12").Copy
ActiveWorkbook.Close
ecolumn = Master.Cells(1, Columns.Count).End(x1ToLeft).Column + 1
ActiveSheet.Paste Destination:=Worksheets("Master").Range(Cells(1, ecolumn), Cells(12, ecolumn))
MyFile = Dir
Loop

End Sub