Pasting in merged cells

[Drompre]

I am getting an error message when trying to paste a cell
into a merged cell ("Cannot change part of a merged cell").

The problem is intermittent with Excel 2000. Never had the
problem with Excel 97

Does anyone have a solution for this?

Thanks

 

[Dave Peterson]

I don't recall how xl97 worked, but maybe you could just remember the mergearea,
unmerge, copy|paste and the remerge:

Option Explicit
Sub testme01()

Dim destCell As Range
Dim saveMergeAddress As String

With ActiveSheet
Set destCell = .Range("a1")
saveMergeAddress = destCell.MergeArea.Address
destCell.MergeCells = False

.Range("a2").Copy _
Destination:=destCell

.Range(saveMergeAddress).Merge True
End With

End Sub

Or if you're just assigning the value:

Sub testme02()
With ActiveSheet
.Range("a1").Value = .Range("a2").Value
End With
End Sub

A1:F1 was merged. A2 wasn't in my testing.