B
banderson
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).Offset(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).Offset(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
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).Offset(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).Offset(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