Consolidate many WBs into one ...

[Ray]

Hello -

Each of my 30 stores has a file that they update daily. Once per
month, I need to consolidate these 30 workbooks into one Master WB ...
each store has it's own tab. All store WBs are in the same format and
all are kept on the same server -- separate files are maintained to
allow concurrent use of the files and also to prevent 'data loss' from
inadvertent deletions

What's the easiest way to automatically pull in all data from these
files into the master file? Data format like this:
all store WBs kept in same folder: \server\folder1\folder2\DailySales
\
store WB name: DailySales_StoreXXX.xls
Master WB name: Total Daily Sales.xls
store tab name within Master WB: XXX

ALL store WBs will be consolidated into the Master WB...I don't want
to link the files into the Master WB, b/c there would be close to
165000 total links and it would take FOREVER to update!

TIA for any help you can give...
Ray

 

[Ray]

Hello -

Each of my 30 stores has a file that they update daily. Once per
month, I need to consolidate these 30 workbooks into one Master WB ...
each store has it's own tab. All store WBs are in the same format and
all are kept on the same server -- separate files are maintained to
allow concurrent use of the files and also to prevent 'data loss' from
inadvertent deletions

What's the easiest way to automatically pull in all data from these
files into the master file? Data format like this:
all store WBs kept in same folder: \server\folder1\folder2\DailySales
\
store WB name: DailySales_StoreXXX.xls
Master WB name: Total Daily Sales.xls
store tab name within Master WB: XXX

ALL store WBs will be consolidated into the Master WB...I don't want
to link the files into the Master WB, b/c there would be close to
165000 total links and it would take FOREVER to update!

TIA for any help you can give...
Ray

Forgot to add something ...

I think transferring the UsedRange from each store WB is the easiest
way to go ... each one will have the same number of Rows/Columns to
transfer.

along the same lines ... what qualifies as 'used' in determining the
UsedRange? For example, if an entire sheet is blank, would changing
the color of cell Z100 to RED make the UsedRange=A1:Z100 ?

Thanks again,
Ray

 

[okrob]

Hello -

Each of my 30 stores has a file that they update daily. Once per
month, I need to consolidate these 30 workbooks into one Master WB ...
each store has it's own tab. All store WBs are in the same format and
all are kept on the same server -- separate files are maintained to
allow concurrent use of the files and also to prevent 'data loss' from
inadvertent deletions

What's the easiest way to automatically pull in all data from these
files into the master file? Data format like this:
all store WBs kept in same folder: \server\folder1\folder2\DailySales
\
store WB name: DailySales_StoreXXX.xls
Master WB name: Total Daily Sales.xls
store tab name within Master WB: XXX

ALL store WBs will be consolidated into the Master WB...I don't want
to link the files into the Master WB, b/c there would be close to
165000 total links and it would take FOREVER to update!

TIA for any help you can give...
Ray

Check out Ron's Site for some pointers on moving data around and
summarizing sheets. I've found that his tips are excellent, they are
commented nicely, and usually easy to understand, even for beginner
programmers.

http://www.rondebruin.nl/copy3.htm