"PPL" a écrit dans le message de news:
...
Thanks Mishell I'll try your ideas and I'll let you know
Do you have any ideas on how the Formula Bar can accessed directly from
VBA - that way I should be able to obviate messing with the merged cells?

You could try to put a STOP statement in your code.


Phil


"Mishell" wrote in message
...


To UnMerge DestinationArea before Copy.


Sub UnMergeDestinationAreaAndPaste()

Dim rngToCheck As Range, rngCell As Range, rngChecked As Range

Set origin = Sheets("Sheet2").Range("G1:I3")
Set dest = Sheets("Sheet3").Range("B1")

Set rngToCheck = dest.Resize(origin.Rows.Count, origin.Columns.Count)

For Each rngCell In rngToCheck.Cells

If rngCell.MergeCells Then

rngCell.MergeCells = False

End If
Next rngCell

origin.Copy dest

End Sub

Mishell


"PPL" a écrit dans le message de news:
...
I do realize that merged cells are bad news.

I'm stuck with an Excel 2003 worksheet, which contains this dreaded
feature. Users are complaining that on trying to paste text into merged
cells gives Excel Warnings:

"Data on the Clipboard is not the same size and shape as the selected
area. Do you want to paste anyway?"

And:

"Cannot change part of a merged cell."



Interestingly, though if the user hits F2, which places the cursor in
the formula bar, then the paste operation works!



I'm usually reasonably of Ok with VBA; but this one is stumping me.

Using VBA, I'm trying to (1) find a way of firstly trapping the Excel
Application warning(s) above and then, once trapped (2) placing the
cursor in the formula bar and finally (3) Paste the clipboard into the
formula bar.



Has anyone any ideas please?



TIA



Phil