Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidate many WBs into one ...
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidate many WBs into one ...
On Apr 3, 9:10 am, "Ray" wrote:
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidate many WBs into one ...
On Apr 3, 8:10 am, "Ray" wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Consolidate | Excel Discussion (Misc queries) | |||
Consolidate | Excel Discussion (Misc queries) | |||
consolidate | Excel Discussion (Misc queries) | |||
Consolidate | Excel Programming | |||
Consolidate | Excel Programming |