Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update workbook links without opening all workbooks | Excel Discussion (Misc queries) | |||
How to update a master workbook from 3 different slave workbooks? | Excel Discussion (Misc queries) | |||
Update master workbook from individual workbooks | Excel Discussion (Misc queries) | |||
AddName - issues w/ creating with reference to a diffferent workbook. | Excel Programming | |||
Automatic update of code in other workbook | Excel Programming |