Paste Special Formats hanging in Excel

[DrSimon]

We have a large macro-enabled Excel 2010 workbook (c4MB), full of formulae, that is updated weekly but is unwieldy to use for my limited purposes. I am trying to extract a 'lite' version for only 4 sheets, containing only values and formats. Paste Special Value works fine, but on some cells Paste Special Formats hangs.

While I'm trying to do this in VBA, that don't seem to be the problem because the same thing happens if I try to do it as a foregound process. It hangs whether I try to Copy/Paste a whole column or just the relevant contiguous cells in the column.

Has anyone any idea where I could start looking to cure this, or is there is a better way to solve the problem than Copy/Paste Special?

Thanks
Simon

 

[Gruuuu]

Does your sheet use merged cells? That's the only place I've seen it act up like that. And merged cells count as part of the formatting.

If so, I strongly suggest changing all your merged cells from Merged to Centered Across Selection (it's in the cell formatting area, under horizontal alignment.)

If not, you'll have to do a little digging to find out what action causes your macro to spaz out. Try with smaller subsets of copy/paste and try to determine what cell or cells are affected.

 

[DrSimon]

Thanks for such a quick reply.

I had thought of that but discounted it as there are merged cells in the same sheet that don't suffer the same problem. But just in case I used find/replace to remove all merged cells and then searched to check there weren't any. Then I Saved As a different file name and tried again. I'm just going to have to try the hard way and go cells by cell to see where the problems lie.
Interestingly I've tried to Copy/Paste Special INTO problem columns and get the same problem.

 

[DrSimon]

In the end I resolved it by:
a) saving original as an xlsx
b) deleting worksheets I don't need
c) Copy and Paste Special / Values in place

Don't know what the problem was though .