Subtotals

[Emece]

Hi,

I have a sheet with subtotals, when I try to copy only the
subtotals to another sheet it copies all the values, not
only the subtotals.

Hoy can I only copy subtotals?

Thks

 

[Nick Hodge]

Emece

There has to be a better way than this but it works ;-)

Copy the data, including data and subtotals and paste special>Values to a
new sheet. Set and autofilter on this data (Data>Filter>Autofilter) and
select the field with a 'summary' (Sum, Count, etc). In the filter list,
select custom...>Contains and in the case of sum for example type *Total.

This will filter the subtotal rows and this data can be copied and pasted to
a new sheet

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Debra Dalgleish]

Select the Subtotals
Choose Edit>Go To
Click the Special button
Select Visible Cells only, click OK
Choose Edit>Copy
Select the cell where you want to paste
Choose Edit>Paste

 

[Denise]

Nick,

Your solution worked beautifully for me, but in a reverse
sort of way. I need to subtotal data, filter the subtotals
(e.g. for items with a balance > 0), and then copy ONLY
those items sans subtotals over to a new worksheet. And
nothing has been working. Your solution is perfect for
that.

Emece, "Debra Dalgleish"'s solution works really well for
what you seem to need, but if you are going to do that
often, I highly recommend that you copy the "select
visible cells" icon to your toolbar [it's on the Edit menu
under Toolbar>Commands]. Doing the Edit>Go
To>Special>Visible Cells will drive you nuts otherwise.

Best,

Denise Heap
Malvern, Pennsylvania

 

[Denise]

Emece,

It's also worth adding that if you have Excel 2002, you
will have major headaches with subtotals. Service Pack 3
was supposed to fix it, but it didn't. Our IT people
initially thought I was crazy, but they have since
replicated the error.

If you have Excel 2002, be aware that if you subtotal,
remove subtotal, re-subtotal, remove subtotal, and then
try to re-subtotal, Excel 2002 cannot handle it. It
corrupts the entire worksheet.

If you do a lot of worksheet design or work a lot with
subtotals, you may want to go back to Excel 2000, which is
considerably more stable.

Best,

Denise Heap
Malvern, Pennsylvania