software

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

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:
---gt;Col headings (Dept, PartNum, Desc, Price)
---gt;Columns are in the same order.

The data in each wkbk must be named ranges.
---gt;I used rng1111Data for dept 1111's data, rng2222Data for dept 2222, etc
---gt;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)Datagt;Import External Datagt;New Database Query
gt;Databases: Excel Files

Browse to one of The files, pick The data range to import.
---gt;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 Datagt;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-Proquot;optimus_1973quot; wrote:

gt; Hi:
gt;
gt; I have multiple workbook (at least 1 max 4) with a database which varies in
gt; the number of registers (in some cases are 250 in others 450) and i want to:
gt;
gt; - Select the range in workbook1 and copy to a consolidate new book then
gt; - Select the range in workbook2 and copy to a consolidate
gt; - Select the range in workbook3 and copy to a consolidate
gt; - Select the range in workbook2 and copy to a consolidate
gt;
gt; The previous data must keep.
gt;
gt; Can any one helpme with this macro.
gt;
gt; Thanks

Thanks Ron i go to do it and i tell you about.

Optimus_1973

*-*-*-*-*-*-*-*

quot;Ron Coderrequot; wrote:

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