Subtotals & More!!

[BexleyManor]

I have a collegue with an huge list of data, some 8000 lines long, that he sorts using the Subtotal function. Perfect, works a treat. Once he has performed this he then has to manually move each individual range of subtotalled data to a new worksheet which is both time consuming and a pain in the @ss !!

I will post a copy of the workbook as soon as I have it in my possession but if anyone can suggest a way of automating the transfer of the data to the new worksheets I would be eternally grateful.

Please feel free to ask If there is anything that doesn't make sense

[lucas]

collapse the subtotals using the outline controls on the left so that only the totals rows are shown:

To copy just these rows and omit the rows that are hidden, we must select only the visisble rows. Excel provides us with that function under Edit>Goto (F5). The Goto dialog has a Special button on the bottom left: It is marked Special

That button brings up the Go To Special dialog box. On that dialog, choose Visible cells only. Then just copy and paste normally.

Not a vba solution but it is a start I hope

[BexleyManor]

Thanks for the input. I don't seem to have made myself clear, sorry!!
I would actually like to copy some of the data from the subtotals and paste this into a new sheet. I've attached the workbook. You will see three sheets. Main List is the subtotalled one and the other two are what I would like to create using some VBA attached to a command button.

It will make more sense when you take a look at it, hopefully!

[geekgirlau]

This is crying out for a pivot table IMHO ...

[BexleyManor]

Hi Geekgirl & thanks!!

An elloquent solution indeed and so simple too!! Unfortunately my collegue would probably pass out at the sight of a pivot table. He has only just come to accept that command buttons are not the work of the devil, I kid you not!!

Soooo, returning to the earlier request, if anyone can knock up some VBA that I can attach to a command button to acheive the creation of new sheets as in the version1 workbook then I would be eternally greatful.

[BexleyManor]

Damn network connection had me double post!!

Incidentally I tried the pivot option on the actual working version of the workbook with 8000+ lines and it falls over whenever I try to do anything with the table. I'm getting 'Reduce number of column or row fields..' *** Which I've now resolved!!

Unfortunately having run this option by my collegue he's not over confident with the use of pivot tables. If anybody can come up with a VBA answer then God bless you!!!

[mdmackillop]

Hi Bex,
Try the following. I've amended the data slightly just to provide a third test page.
Regards
MD

BTW Check that document properties does not contain info you don't want published.

[BexleyManor]

MD, absolutely stunning work my friend. Hat's off to you.

Does exactly what it say's on the tin!!

I shall run this by my collegue tomorrow and I'm pretty sure he'll have a big fat smile on his face!!

I'll drop by and let you know.

Thanks again mate, superb work.

Oh, one more thing. Is that a Rhodesian Ridgeback wearing the cap on your pic ??