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

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

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

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!!


JLGWhiz wrote:
I think that instead of this:

Basebook.Worksheets(1).Range("A1").End(xlColumns). Offset(0, 2)

I would use this:

Basebook.Worksheets(1).Range("A1").End(xlToRight). Offset(0, 2)