How to paste data into sheet?

[Guest]

What would be the best way to copy data from one sheet to another where as
the cells of the copy to sheet are merged and not the same size as the copy
from sheet. I have check other posts and they all say to not use merged cells
and they have to be the same size. Is there a way around this?

 

[Jim Rech]

Maybe I'm not remembering correctly but I recall this is only a problem when
you do a paste special, values. If that's your problem maybe you can do a
paste special, formulas. Alternatively do a Value = Value (e.g.,
range("A1:A10").Value = range("B1:B10").Value. Alternatively remove the
merged cell, paste, reset merged cell.

--
Jim
| What would be the best way to copy data from one sheet to another where as
| the cells of the copy to sheet are merged and not the same size as the
copy
| from sheet. I have check other posts and they all say to not use merged
cells
| and they have to be the same size. Is there a way around this?

 

[Bill Renaud]

I am assuming that you are copying data from a source data workbook into a
template that happens to have some cells that are merged for some purpose.

Experiment with the MergedCells (Boolean) and MergeArea (Range) properties
of a cell on the "Copy to" worksheet. They will tell you if the destination
cell has been merged with other cells or not. Step through the code below
and inspect the Locals window to see how it works. Check Excel Visual Basic
Help also.

When a cell has been merged with other cells, then you have to use the
MergeArea to copy to, as in the following routine (which assumes both
rngFrom and rngTo are single cells). Basically, the value gets put in the
upper-left corner cell of the rngTo range, and the other cells in the
MergeArea are left empty.

Public Sub CopyToMergedCell(rngFrom As Range, rngTo As Range)
If rngTo.MergeCells _
Then
rngFrom.Copy Destination:=rngTo.MergeArea
Else
rngFrom.Copy Destination:=rngTo
End If
End Sub