What I am trying to do is use VB Code to take data from 4 files within
a folder and copy the data
to a master file in another workbook.
Each file in the folder contains data in columns A&B, and has a dynamic
amount of rows depending on the file.

The following code is what I have so far, which works(kind of)!!!
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(xlColumn).O ffset(0, 2)
myBook.Close
Next i
Basebook.SaveAs Application.GetSaveAsFilename("FREIGHT_MASTER")
End If
End With


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

End Sub

This code gives me the first and last record within my folder, but if i
change
Range("A1").CurrentRegion.Copy _
Basebook.Worksheets(1).Range("A1").End(xlColumn).O ffset(0, 2)
to be xlToRight it gives me an error:
Run-time Error '1004'
Application-Defined or object-defined error

If I use the same line of code and put in xlDown it works, grabs all
data from 4 worksheets and
copies them to master file but all in the first column down.

I want it to copy all the data across and not down.

If anyone could shed some light on how to go about this to finish off
my code to make this work
it would be greatly appreciated.
Thanks

Alok,
You are the man
Worked like a charm!!!
Thank you for seeing what I couldn't!!!

Alok wrote:
Try the following- assumes that all files have data in only 2 columns

Range("A1").CurrentRegion.Copy _
Basebook.Worksheets(1).Cells(1,(i-1)*2+1)

Alok

" wrote:

What I am trying to do is use VB Code to take data from 4 files within
a folder and copy the data
to a master file in another workbook.
Each file in the folder contains data in columns A&B, and has a dynamic
amount of rows depending on the file.

The following code is what I have so far, which works(kind of)!!!
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(xlColumn).O ffset(0, 2)
myBook.Close
Next i
Basebook.SaveAs Application.GetSaveAsFilename("FREIGHT_MASTER")
End If
End With


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

End Sub

This code gives me the first and last record within my folder, but if i
change
Range("A1").CurrentRegion.Copy _
Basebook.Worksheets(1).Range("A1").End(xlColumn).O ffset(0, 2)
to be xlToRight it gives me an error:
Run-time Error '1004'
Application-Defined or object-defined error

If I use the same line of code and put in xlDown it works, grabs all
data from 4 worksheets and
copies them to master file but all in the first column down.

I want it to copy all the data across and not down.

If anyone could shed some light on how to go about this to finish off
my code to make this work
it would be greatly appreciated.
Thanks