**rather than copying data cells**

*how to copy the cells showing sub totals***and**subtotals.

I could not find an easy solution. Below I describe a two-step solution which basically consists of

- Applying a filter to show only the sub total rows
- Using
*Copy / Paste special*to get a copy of the sub totals (without the formulas)

### The data

Assume you have 2 columns of data like this:### The sub totals

** Data -> Subtotals...** and then ticking

**X**and

**OK**will result in adding extra rows for sub totals.

This was the starting point of the question being asked.

### Applying a filter

**and enter**

*Data -> Standard Filter*- 2 filter criteria for X
*.*Sum*and*.*Total*in order to capture both the*Sum*and*Total*rows - Tick
*Regular expression* - Tick
*Copy results to...*and enter a cell on the sheet (A16 in this example)

(this is important: don't copy to another sheet since the formula won't work)

### Copy the result

The filter resulted in A16:B20. Two things to note:- There are no data rows anymore
- Column B still contains the formulas

### Paste special

Now use*Paste special*to paste the sub totals into a new position. Deselect

**Formulas**in order to copy the data only. Ensure that everything else is ticked, in particular

**Numbers**.

### The result

A copy of the sub totals in D16:E20.Note that column D does not contain formulas.

excellent, thanks your post save me a bounch of time! Regards.

ReplyDeletefull this version,,

ReplyDeleteI've been looking for this for years. Thank you!

ReplyDeleteThat's so good - thanks!

ReplyDeleteBonus tip: I use " Sum$" (without the quotes ) in the Find and Replace dialog - because that has a RegEx option as well. Now I've removed the " Sum" text from the end of each row :-)