Using VB Code to update one workbook with data from 4 diffferent workbooks.

[banderson]

Alright I have gotten this far:
Sub Consolidate()
With Application
.DisplayAlerts = False
.EnableEvents = False
.ScreenUpdating = False
End With


With Application.FileSearch
.NewSearch
'Change this to your directory
.LookIn = "C:\Documents and Settings\banderson\Desktop\freight
thing"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
Set Basebook = Workbooks.Open(.FoundFiles(1))
For i = 2 To .FoundFiles.Count
Set myBook = Workbooks.Open(.FoundFiles(i))
Range("A1").CurrentRegion.Copy _
Basebook.Worksheets(1).Range("A1").End(xlColumns).Offset(0, 2)
myBook.Close
Next i
Basebook.SaveAs
Application.GetSaveAsFilename("FREIGHT_MASTER.xls")
End If
End With

With Application
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With

End Sub

Each Individual workbook is in the Designated Folder indicated above in
my code.
Each Workbook contains 1 sheet of data with 2 columns Freight Vendor,
Amount
I have the code read in all the workbooks with an .xls extension and
copy to the new
workbook with a for statement until it doesn't have any more .xls files
to read in.

The problem I have having is trying to get all four workbook(data) to
copy over
side by side which I set with:
Range("A1").CurrentRegion.Copy _
Basebook.Worksheets(1).Range("A1").End(xlColumns).Offset(0,2)

But with this line of code it only seems to bring across the first
workbook it sees and the last workbook it sees and leaves out the other
2 workbooks in between?
Can someone point out the obvious to me as to why it doesn't copy all
the workbooks data??
Any help would be greatly appreciated.
Thanks

 

[banderson]

JLGWhiz,
I tried changing that statement but to no avail.
It still gives me only the first and last record in my folders.
But if I use the command of (xlDown).Offset(0,2)
it brings in all the files for me.
Strange.
Thanks for the help!!