Copy visible cells

[Peter100]

I have a database which i have used subtotals on. Can I copy just the subtotals to another sheet without selecting the individual cells ie close the subtotal view to only show the subtotal values , highlight them all then copy and paste ?

 

[NateO]

Howdy Pete, Try, alt-; -> Ctrl-c. Go to your next sheet, ctrl-v.

 

[Blacky]

Select the area you want to copy. Press F5 click Special, click visible cells only click OK. Copy, then paste where you want.

There is a toolbar button for visible cells only if you use it lots.

HTH

 

[Peter100]

Well done and thank you

 

[kleino]

after I choose "visible cells only" nothing happens.i.e. the paste does not show anything

 

[Peter_SSs]

after I choose "visible cells only" nothing happens.i.e. the paste does not show anything

Welcome to the MrExcel board!

The steps are:

1. Select the (collapsed) Subtotals range.

2. Alt+; (or Blacky's method) to select the visible cells only.

3. Copy

4. Move to where you want and Paste.

 

[EMF]

Great help, thanks a lot.

 

[MarkS123]

Note: This only works if you paste into a worksheet that was opened in the SAME INSTANCE of Excel. If you try to paste into a worksheet in a new instance of Excel it will paste all the cells, not only the visible ones (for Excel 2007).

 

[Peter_SSs]

Note: This only works if you paste into a worksheet that was opened in the SAME INSTANCE of Excel. If you try to paste into a worksheet in a new instance of Excel it will paste all the cells, not only the visible ones (for Excel 2007).

Sure, but I guess that wouldn't be such a common occurrance.
In any case a few extra steps should achieve the same result.

After copying to the second instance of Excel ..

1. Apply AutoFilter
2. (Custom) Filter the relevant column for 'does not end with' ' Total' (or ' Count', ' Average' etc - depending on your type of SubTotal)
3. Select & delete all the rows below the heading row
4. Remove the AutoFilter