MrExcel Publishing
Your One Stop for Excel Tips & Solutions

copy subtotals only, is this possible


Posted by denis on December 16, 2001 5:59 AM

Can I copy only the subtotals generated by excel & paste them to a new sheet.

Thanks in advance!


Posted by Tom Urtis on December 16, 2001 7:30 AM

Yes, it is possible manually or with VBA. Taking it from the top...

Manually, here is one of a few ways:

Just for convenience before anything, right-click on your worksheet menu bar, left click on Customize, click on the Commands tab, choose the Edit category, and towards the bottom of that list is a 4-black-square looking icon named "Select Visible Cells". Use your left mouse button to drag that icon up to your toolbar. Click the Close button to return to the worksheet.

Run your subtotal, and highlight the collapsed portion you want to copy. Click on that "Select Visible Cells" icon. Now click Edit > Copy, then go to whatever sheet/range you want to copy it to, and select Edit > Paste.

Using VBA you could add something to your macro like:

ActiveSheet.Range("A4:B24").SpecialCells(xlCellTypeVisible).Copy _
Destination:=Sheets("Sheet3").Range("C37")

Hope this helps.

Tom Urtis

Posted by denis on December 16, 2001 8:14 AM

thanks tom, worked perfect

Thanks