Copying values of merged cells

I have a pair of workbooks in which one calculates a number of values and I want to store the values only in the other.

I would expect to be able to highlight the range of the calculated cells in the source workbook, Copy and then Paste Special | Values in the other.

Both the source and the target contain some merged cells. When I try to do the paste, it complains that "this operation requires the merged cells to be identically sized".

They are identically sized.

I have even done Paste Special | Column Widths to guarantee they are identically sized. It doesn't complain about the merged cells then; in fact, it doesn't complain about the merged cells with any of the other paste operations, only with Values.

I have been able to repeat the behaviour with a very simple example. See the two attached sheets. Try pasting Values only of the range $B$2:$D$4 from either one to the other.

When I first created the samples they worked. What seemed to triggered it is I changed one of the merged column widths by one pixel, tried expecting it to fail (and it did), changed that column width back again and it hasn't worked since. As I said, I even tried pasting the Column Width to the target, which it appeared to do successfully, but it hasn't helped.

Now, the attached examples are only to show the principle of the problem. They are very simple and there would be a myriad alternative work-arounds for them. In my original case though there are many formulae over a much larger range and I want other people to be able to paste values without having to follow complicated instructions, so I am not looking for work-arounds unless someone has a mind-numbingly simple one that my numb mind has overlooked.

The only other way of solving the problem that I can think of would be to write a macro that copies cell values individually, but I'd rather not do that because I know these people: they will get narky about having to enable macros. It will be "against organisation policy" or something.

Is there any way of resetting the target workbook so that it will receive the values from the source?

Yeah. You know that and I know that. But I've been given this spreadsheet and my job is to make it work as is. If I start unmerging cells then it is going to affect something else further on down the chain. We both know that too.

Suffice to say, I am not at liberty to change part of the customer's system that is integral with, or may have flow-on effects, to other parts. I have been given this problem and asked to make it work.

Excel does not seem to be working according to specification. It is saying the cells are not identical when in fact they are. Of particular note, the copy of values did work when the pair of sample spreadsheets were first set up. The behaviour altered when the column was made a different width and did not revert to its initial condition when the column was restored to its former width. No other change was made for this simple example.

It suggests that, despite the width having been restored, some other property is also altered when a column width is changed that is preventing the copy from taking place.

I had hoped, in posing the question, that someone would recognise this as a known problem and be able to offer the solution as to which property needs to be reset, or what other action needs to take place, in order for the copy to work again.

What happens when you clear the contents of the destination cells first?

The same thing happens, Tony.

I ran into this problem a while ago, I'll see if i can remember how i got around it and let you know

Righto,

i did this inside a macro, however

1. copy then paste - do not paste special

2. In the destination work sheet while the cells are still highlighted go copy, paste special paste values over the top and it should work.

Hope that helps

tony

Tony, good on you mate. I bit the bullet and went the macro route and have done something very similar. Since I am having to write a macro anyway, I get it to create a new workbook, paste the values then paste the formats and it works wonderfully.