"Cannot change part of a merged cell"

[abas]

Hi there.

I am having difficulty with a worksheet that has some merged cells in it.

I am trying to copy an entire worksheet so I select all the cells and click on copy at which point I get the message "Cannot change part of a merged cell".

Now, I can understand getting this message when I attempt to paste the contents but not at the "copy" stage of the process.

I created another worksheet with only a few details in it and included some merged cells and when I copy the entire worksheet it works fine so the problem is related to the initial worksheet somewhere.

Any ideas anyone?

 

[Michael M]

Yep, get rid of the merged cells......they'll give you nightmares anyway !!
Instead, highlight the cells you want merged and select Format Cells / alignment
and in the Horizontal dropdown box, select
"Center across Selection"
It does the same thing, but gets rid of those nuisance merged cells !!

 

[abas]

Thanks Michael,

I agree with your recommendation, however there must be a solution somewhere in the formatting of the worksheet that is preventing the copy function as I can open a new worksheet (in the same workbook) create some merged cells and they copy with no problems at all.

I also tried to delete all the data by selecting all and pressing delete and I get the same message if the merged cells are part of the selection.

This spreadsheet is not mine, I was just called on to see if I could assist so don't want to confuse the user too much.

Thanks for your suggestion though, I'll be using that in the future.

Alan

 

[Cindy Ellis]

If your intention is to copy the entire sheet, right-click the tab name, select move or copy, then check the "Create a Copy" checkbox, then click OK. The entire sheet will be copied as a whole, merged cells and all.
Hope that helps,

 

[abas]

Thanks Cindy,

Yes that is the intention but to a totally different workbook.

Good suggestion though

 

[Michael M]

Open the 2 workbooks side by side
Select the tab name, then hold down the CTRL key and drag the highlighted worksheet across to the new workbook.

 

[abas]

Excellent Michael,

That worked. Thankyou very much

Alan

 

[Michael M]

Glad it worked.....and thanks for the feedback....

 

[abenitez77]

Open the 2 workbooks side by side
Select the tab name, then hold down the CTRL key and drag the highlighted worksheet across to the new workbook.

can this be done with vba code?