Copy merged cell, paste as merged cell

**jomili** · 12-17-2009, 03:21 PM

I have a macro to copy a merged cell from one sheet and paste it to another as a merged cell. Basically, I want to copy it AS IT IS and paste it AS IT IS. My code is below.

'Copy and paste the Procedure
    Sheets("Steps").Range("J2").Copy
    Sheets("Results").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

I can manually do a copy and paste and it works fine, the pasted object stays as a merged cell. How can I do it in this macro?

Thanks in advance.

**TimK** · 12-17-2009, 06:12 PM

The following works for me.

On 'sourcesheet' cells A1 & A2 had been merged.
After being run 'Targetsheet' then shows a merged cell at "A4" with data from "A1".

Sub test()

    Dim r01 As Range, r02 As Range, r03 As Range
        
    Set r01 = ThisWorkbook.Worksheets("SourceSheet").Range("A1")
    Set r02 = ThisWorkbook.Worksheets("TargetSheet").Range("A4")
    Set r03 = ThisWorkbook.Worksheets("SourceSheet"). _
                              Range("A1").MergeArea
 
    r03.Copy
    r02.PasteSpecial xlPasteAll
    
End Sub

Hope this helps

**jomili** · 12-18-2009, 11:30 AM

Thanks. That one works great!

**royUK** · 12-18-2009, 02:27 PM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save