Solved: create new workbook B from subtotals in workbook A

[K. Georgiadis]

I am not optimistic about the feasibility of this, but let me try anyway:

1) I have a large workbook (20 megs) with sales data for 2003, 2004 and 2005.

2) For each year there are two worksheets: one sorted by distributor, the other sorted by product. Total 6 worksheets

3) In each sorted worksheet, I have inserted subtotals using Excel's built-in Data>Subtotals function

4) For further analysis, I want to transfer the subtotals ONLY to a brand new workbook, which should be more manageable in size. This is all the more important because the final analysis will have to be disseminated to various people as an email attachment. Even when zipped, the original file exceeds the size allowed by most ISPs.

Can the creation of the new workbook with Subtotals only be automated with VBA?

[mdmackillop]

Can you extract and post a small sample showing what you want to extract and how it is to be presented. I guess you either need some headers/labels to go with the data, or you need to inset the data into a template.

[mdmackillop]

From PM
Imagine 6 worksheets with 8 columns and 24444 rows of data each. I sorted the data alphabetically and then in descending order to show those distributors and products that accounted for most of the sales. Only one of the columns contains values (sales $) and those have been captured with Excel's Subtotal function. Following the calculation of the subtotals, I collapsed the sorted data to level 2 and I want to continue further work with those subtotals only-- no more than 100 rows of numbers. So now the question is whether I can create automatically the new abbreviated workbooks. I remember that Excel has the built-in capability of copying subtotals only to another location but I must relearn how to do that too (any way, if memory serves, by this method the subtotals can only be copied to another worksheet within the same workbook, which may not exactly solve my problem).

[mdmackillop]

Have a look at this, (http://www.dailydoseofexcel.com/archives/2004/04/19/copy-subtotals-only/) although it's not working for me in 2003

[K. Georgiadis]

Thank you very much for the link; after my PM I went back and figured out how to copy the visible rows only. I tested it and determined that, contrary to what I remembered, I can paste the visible rows to a new workbook. Since the data are static and do not need to be updated, I'll do this simple copy>paste and proceed from there. Initially, I was thinking of using a Ferrari but I have since decided that a wheelbarrow will be sufficient:) Thanks again! I'll mark this as solved.