Copy merged cells making target also a merged cell

Question

I have a merged cells A1:B1 I would like to copy that merged cell, however i would like to have a source cell merged in the same way.

Sub Copy()

Sheet2.Range("A1").Copy
Sheet2.Range("A5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

End Sub

Using this code I will paste value of A1 to A5, however A5 wont be merged with B5, and that is the result I am looking for.

Answer 1

You can specifically tell the destination cell to become merged. And you can specify the merge area by counting the rows and columns of the source cell's merged area.

Sub Example()
    Dim Src As Range
    Set Src = Range("A1")
    
    Dim Dst As Range
    Set Dst = Range("A5")
    
    Dst.Value = Src.Value
    
    Dst.Resize(Src.MergeArea.Rows.Count, Src.MergeArea.Columns.Count).Merge
End Sub

For copying formatting, in addition to the code above, add these lines:

    Src.Copy
    Dst.PasteSpecial xlPasteFormats
    Application.CutCopyMode = False

But since you're copying values and formatting, its probably easier to copy the whole range instead of doing one and then the other:

Sub Example2()
    Dim Src As Range
    Set Src = Range("A1")
    
    Dim Dst As Range
    Set Dst = Range("A5")
    
    'Copy Src to Dst
    Src.Copy Dst
    
    'Merge Dst like Src
    Dst.Resize(Src.MergeArea.Rows.Count, Src.MergeArea.Columns.Count).Merge
    
    'The first row of Dst may accidentally change height during the copy/paste
    Dst.Rows(1).RowHeight = Src.Rows(1).RowHeight
End Sub