You might be able to use MS Query to consolidate Excel ranges from your
multiple wkbks/wkshts. This also works for consolidating data from the
active workbook (Just save it first so Excel can find it):
My example uses 5 named ranges in 5 different workbooks.
(Each range contain 4 columns: Dept, PartNum, Desc, Price)
Assumptions:
The data in each wkbk is structured like a table:
--->Col headings (Dept, PartNum, Desc, Price)
--->Columns are in the same order.
The data in each wkbk must be named ranges.
--->I used rng1111Data for dept 1111's data, rng2222Data for dept 2222, etc
--->You may use the same range name in different wkbks.
(Note: MS Query may display warnings about it's ability to show the query
...ignore them and proceed.)
Starting with an empty worksheet:
1)Select the cell where you want the consolidated data to start
2)Data>Import External Data>New Database Query
Browse to one of The files, pick The data range to import.
--->Accept defaults until the next step.
At The last screen select The View data/Edit The Query option.
Click the [SQL] button
Replace the displayed SQL code with an adapted version of this:
SELECT * FROM `C:\Inventories\Dept1111`.rng1111Data
union all
SELECT * FROM `C:\Inventories\Dept2222`.rng2222Data
union all
SELECT * FROM `C:\Inventories\Dept3333`.rng3333Data
union all
SELECT * FROM `C:\Inventories\Dept5555`.rng4444Data
union all
SELECT * FROM `C:\Inventories\Dept5555`.rng5555Data
Return the data to Excel.
Once that is done....to get the latest data just click in the data range
then Data>Refresh Data.
You can edit the query at any time to add/remove data sources and/or fields.
Does that help?
***********
Regards,
Ron
XL2002, WinXP-Pro
optimus_1973 said:
Hi:
I have multiple workbook (at least 1 max 4) with a database which varies in
the number of registers (in some cases are 250 in others 450) and i want to:
- Select the range in workbook1 and copy to a consolidate new book then
- Select the range in workbook2 and copy to a consolidate
- Select the range in workbook3 and copy to a consolidate
- Select the range in workbook2 and copy to a consolidate
The previous data must keep.
Can any one helpme with this macro.
Thanks
