Hello Excel VBA Gurus!
I am working on an excel to compile data from separate locations. With the help of many other great minds I have reached this point.
Sub LoopThroughDirectory()
Dim MyFile As String
Dim erow
fpath = Worksheets(1).Range("B3").Value & "\"
MyFile = Dir(fpath)
Do While Len(MyFile) > 0
If MyFile = "zmaster.xlsm" Then
Exit Sub
End If
Debug.Print Now, MyFile
Workbooks.Open fpath & MyFile
Range("A2:D3").Copy
ActiveWorkbook.Close
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Worksheets("sheet2").Paste Destination:=Worksheets("sheet2").Cells(erow, 1)
MyFile = Dir
Loop
fpath = Worksheets(1).Range("B4").Value & "\"
MyFile = Dir(fpath)
Do While Len(MyFile) > 0
If MyFile = "zmaster.xlsm" Then
Exit Sub
End If
Debug.Print Now, MyFile
Workbooks.Open fpath & MyFile
Range("A2:D3").Copy
ActiveWorkbook.Close
erow = Sheet2.Cells(Rows.Count, 7).End(xlUp).Offset(1, 0).Row
Worksheets("sheet2").Paste Destination:=Worksheets("sheet2").Cells(erow, 7)
MyFile = Dir
Loop
End Sub
At this point this file can pull various spreadsheets from within two folders that I specify in the first sheet, copy them, and then paste them into my spreadsheet side by side. My problem arises that in the final product it will need to look inside of folders within the one specified to find the spreadsheets. Each one of the folders within the specified folder has one spreadsheet and a bunch of unimportant folders.
Any thoughts?
Many Thanks
Bookmarks