Can't paste into a merged cell

[John Dice]

I have a macro that copies data from different worksheets
and pastes the data into a main sheet for printing.

In one part, the macro copies a long number from a single
(unmerged cell) on one sheet and tries to paste into a
merged cell (formerly 3 horizontal cells) on the Print
Sheet. I get an error message that says, "Cannot change
part of a merged cell."

This confuses me because I am able to copy data from the
SAME sheet and paste it into the nearby merged cell...I
just can't do it from a different sheet. Why?

Thanks

[Debra Dalgleish]

Use the full address of the merged cell, and you should be able to paste
the single cell. For example, instead of pasting into cell Range("A2"),
paste into Range("A2:C2"):

Sheets("Sheet2").Range("C3").Copy _
Destination:=Sheets("Sheet1").Range("A2:C2")

John Dice wrote:

--

Debra Dalgleish

Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

[John Dice]

Well that makes sense but I still get the same error
message.

Here's my actual code, with your suggestion:

ActiveSheet.Range("E1").Copy Destination:=Sheets("Form
195").Range("I4:K4")

I use ActiveSheet because the macro copies data from the
currently active sheet which may change depending on where
the user is.

The macro copies many other cells (all to and from
unmerged cells) and they all work fine. Its just this one
merged cell that blocks the macro.

John Dice

>.
>

[Debra Dalgleish]

Your line of code works for me in Excel 97, 2K and 2002.

Is it possible that this cell is also merged with other cells? Perhaps
there is a hidden row above or below it, and it's merged with cells there.

[John Dice]

Nope. I double checked and all rows and columns are
accounted for. I was able to work around it by un-merging
the cell. This messed up some of my formatting but I can
make do. Its not as pretty as before but what the heck.

I am using 2000. Thanks for your help. I obviously have
a bug somewhere in my code or layout and I will keep
looking. At least I know now that it SHOULD work and I'm
not trying to do an impossible task.

Thanks again,

John

>.
>

[Debra Dalgleish]

You can make the cells look pretty again:

1. Select cells I4:K4
2. Choose Format>Cells
3. On the Alignment tab, from the Horizontal dropdown, choose 'Centre
Across Selection'
4. Click OK

You could add an outside border around the group of 3 cells to enhance
the illusion that they are merged.

With this technique, you get the appearance of merged cells, but none of
the headaches.

[Dave Peterson]

You could generalize Deb's code a little. This worked ok for me xl2002:

Sheets("Sheet1").Range("C3").Copy _
Destination:=Sheets("Sheet2").Range("A2").MergeArea

Am I too late?

--

Dave Peterson
ec3...@msn.com

[John Dice]

That works! Thanks a ton for the "center across" tip.
The cell formatting is lost when I paste but I just have
the macro reformat the new cell with the "center across"
alignment and it looks just like I want it to.

Thanks,
John Dice

>>>>>http://www.contextÍ{ wÀ Q <aw
>,ð>QtS ì¸ ures.com/tiptech.html

>>>>>
>>>>>.
>>>>>
>>>>>
>>>>>
>>>
>>>--
>>>
>>>Debra Dalgleish
>>>
>>>Excel FAQ, Tips & Book List
>>>http://www.contextures.com/tiptech.html
>>>
>>>.
>>>
>>>
>
>
>--
>
>Debra Dalgleish
>
>Excel FAQ, Tips & Book List
>http://www.contextures.com/tiptech.html
>

>.
>

[Harlan Grove]

John Dice <dice...@earthlink.net> wrote...

>Nope. I double checked and all rows and columns are
>accounted for. I was able to work around it by un-merging
>the cell. This messed up some of my formatting but I can
>make do. Its not as pretty as before but what the heck.
>
>I am using 2000. Thanks for your help. I obviously have
>a bug somewhere in my code or layout and I will keep
>looking. At least I know now that it SHOULD work and I'm
>not trying to do an impossible task.

...

For the heck of it try something like

Worksheets("Sheet2").Range("C3").Copy _
Destination:=Worksheets("Sheet1").Range("A2").MergeArea

which would ensure you're pasting into the entire merged area.