MrExcel Publishing
Your One Stop for Excel Tips & Solutions

is it possible copy a range of single cell to merged cell

Posted by Roy Sletcher on July 09, 2001 8:26 PM

This message board is fantastic. A quick and sincere thank you to the many guru's here who have unknowingly solved many of my other problems.

I am tearing my hair out trying to copy a single cell to a merged cell on another worksheet via VBA.

The relevent portions of the code that fails are a follows:

Dim range1 As range
Dim range2 As range

Set range1 = Sheets("Paper").range("E12")
Set range2 = Sheets("Estimate").range("C39")

range1.Copy range2

' range2 is a merged cell which comprises cells C39:G39 and is referenced as C39 on the desktop. I am assuming this is the problem leading to a Runtime 1004 error. I have tried several variation of this code always with same problem.

' I have simplified this examble by removing all extraneous material to help clarify the problem.

' Please Help.

Posted by Damon Ostrander on July 09, 2001 10:30 PM

Yes, it is possible.

Actually, if all you want to do is put the value of range1 into range2, but not the formats, etc., you don't have to copy and paste. All you have to do is set the value directly:

range2.value = range1.value

If on the other hand you want use copy and paste (for whatever reason), you must unmerge the destination cells before doing the paste, then re-merge it:

Sheets("Estimate").[C39:G39].MergeCells = False
range1.Copy range2
Sheets("Estimate").[C39:G39].MergeCells = True

Happy computing.