Copy merged cells making target also a merged cell-CodePudding

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.

CodePudding user response：

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