Find Totals and copy them to another worksheet

[errtu]

I will have a table and I'll apply subtotals to it.

I want a macro to find the Commodity Totals and copy those two cells and also the data in the cell to the right and top. Like so:

-------A------------------------B--------------------------C-------
Commodity-----------------#'s-----------------------Price-----
----Cans------------------12------------------------------15.99----
----Cans------------------32------------------------------15.99-----
----Cans------------------78------------------------------15.99----
CANS TOTAL---------122------------------------------ ---------
----Boxes------------------10------------------------------5.99----
----Boxes------------------20------------------------------5.99-----
----Boxes------------------15------------------------------5.99----
BOXES TOTAL---------45------------------------------ ---------

So, the macro should find the total generated by the subtotals button, copy CANS TOTAL, 122 and the value in C4 (15.99) paste it in Worksheet1, then go on and find the next total until it runs out of totals to copy.

 

[GlennUK]

Adding subtotals generates an outline ... you can collapse the outline to show only subtotals ... then select only visible cells to copy.

 

[errtu]

Adding subtotals generates an outline ... you can collapse the outline to show only subtotals ... then select only visible cells to copy.

that's an idea, and the c4 value?

 

[GlennUK]

Explain why C4 is to be copied ... do you want the price that is associated with the subtotal to be copied? Are all prices the same for a commodity?

 

[errtu]

Explain why C4 is to be copied ... do you want the price that is associated with the subtotal to be copied? Are all prices the same for a commodity?

The prices vary for each commodity. Yes, I want the price associated with the subtotal (and that commodity by extension) copied.

 

[GlennUK]

When you add subtotals, include Price too ... afterwards select the Price column, and do Edit/Replace of (9 to (1 and click the Replace All button. Collapse your outline to show only totals, select the data area, copy, then Paste Special Values whereever you want.