|Posted by davesexcel on November 26, 2012 at 12:45 AM|
Get data from many workbooks in a folder.
This code will loop through a folder called "WorkBookLoop".
The code then Copies and pastes whatever is in column A:B and paste it to the workbook that is running the code.
Dim MyFile As String, Str As String, MyDir As String, Wb As Workbook
Dim Rws As Long, Rng As Range
Set Wb = ThisWorkbook
'change the address to suite
MyDir = "C:\WorkBookLoop\"
MyFile = Dir(MyDir & "*.xls") 'change file extension
Application.ScreenUpdating = 0
Application.DisplayAlerts = 0
Do While MyFile <> ""
Rws = .Cells(Rows.Count, "B").End(xlUp).Row
Set Rng = Range(.Cells(2, 1), .Cells(Rws, 2))
Rng.Copy Wb.Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
Application.DisplayAlerts = 1
MyFile = Dir()