Excel VBA Macro - Copy and insert copied cells

excel, vba

Solution

Try this

Sub CopyConcatenate()
    Dim ws As Worksheet
    Dim rng As Range
    
    '~~> Set this to the relevant worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    With ws
        '~~> Set your range
        Set rng = .Rows(ActiveCell.Row)
        
        '~~> Copy the range
        rng.Copy
        
        '~~> Insert the range
        rng.Offset(5).Insert Shift:=xlDown
        
        '~~> Clear the clipboard. More importantly remove the
        '~~> ant like borders
        Application.CutCopyMode = False
    End With
End Sub

Problem

I can't seem to make to this work. I keep getting stuck on ``` Rows(rng 5).Select ``` What I am trying to do is copy the row of the active cell and inserting the copied cell in a row that is 5 rows below the active cell. ``` Sub CopyConcatenate() Dim ws As Worksheet Dim rng As Range Set rng = ActiveCell.Rows rng.Select Selection.Copy Rows(rng 5).Select Selection.Insert Shift:=xlDown End Sub ```

Original source

Related problems