Copy, paste special - values only. But keep groups on rows!

**Emmaly** · 12-08-2011, 11:04 AM

Dear All,

I am sure this is an easy one, but it was driving my manager crazy and he can't get it to work.

We have a large spreadsheet that is grouped on the rows so we can see summaries of each section

However the sheet is full of OLE's so we need to copy and paste special as values only so it doesn't bring the formulae through too.

However even if we copy the format too it won't keep the groupings at the side only along the top on the columns.

Please can someone help as it is only a simple thing but will save so much time

Thanks

Emmaly

**TMS** · 12-08-2011, 11:14 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

Regards, TMS

**Emmaly** · 12-08-2011, 11:40 AM

here is a sample of the data with nonsense numbers in it. We want to copy and paste it as values only but also keep the grouping on the rows. If i do paste special values only and then do formats too it only keeps the grouping along the top. Rather frustrating!Sample.xlsx

**TMS** · 12-08-2011, 11:59 AM

This seemed to work for me though, to be honest, there were only values and no formulae.

First, expand all the groups.
Select Cell A1
Press Ctrl-Shift_End to select all the cells
Press Copy
Press Paste Special | Values
Press Paste Special | Formats

Done.

I can still expand the groups down and across so, hopefully, you can too.

Regards

**Emmaly** · 12-08-2011, 12:13 PM

Hi,

Thanks for the reply. Irritatingly though it doesn't work. I can paste the group along the top fine but if i follow your instructions i lose the grouping at the side.

Isn't it bonkers, it should work. Logic tells me it should work, but it doesn't. I think i am actually going to go crackers!!!

**TMS** · 12-08-2011, 02:02 PM

Ah, I think I may have misunderstood. I thought you were copying the values in place.

OK, why not just copy the sheet, then copy and paste special values & formats on the new sheet.

Then use the copy to circulate, or whatever.

Regards