Trouble with macro for extracting data from multiple workbooks into one workbook

[salman350z]

Hi,

I am using Excel 2010. I am attempting to write a macro that will open exactly 938 excel workbooks, copy the specified data, and then paste it into one master excel workbook. Most(99%) of the data in the 938 workbooks is in the same order.

Once the relevant data is copied, I would like to open a master workbook, and paste it in a specific order and then loop to the next workbook. The Excel workbooks are all the same. They are numbered, for example, the following way: 54-4xxxA, where the x's are the only variable quantity. All the excel workbooks are placed in a folder with no subfolders in .xls format.

I have the following code so far, it runs without any errors, however, it doesn't perform any work. Please help me correct my code.

Option Explicit


Sub loopthroughdirectory()
Dim MyFile As String
Dim emptyrow As String


MyFile = Dir("C:\Users\sadil\Desktop\Rider Bands Project\rider bands excel files")
Do While Len(MyFile) > 0
Workbooks.Open (MyFile)
Range("C2").Copy
ActiveWorkbook.Close
emptyrow = Sheet2.Cells(Rows.count, 1).End(xlUp).Offset
ActiveSheet.Paste Destination:=Worksheets("sheet2").Range(Cells(emptyrow, 1), Cells(emptyrow, 4))
MyFile = Dir
Loop
MsgBox ("we are here")
End Sub