Excel VBA Macro - Copy and insert copied cells

Question

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

What are you trying to do in Rows(rng 5).Select ? This should through an error cause the syntax is incorrect. — Pankaj Jaju
– Pankaj Jaju, Commented Jan 30, 2014 at 11:52
I'm trying to select the row that is 5 rows below the one with the active cell. — NoNo
– NoNo, Commented Jan 30, 2014 at 11:55

Nimantha · Accepted Answer · 2022-01-07 08:22:37Z

20

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

edited Jan 7, 2022 at 8:22

Nimantha

6,5376 gold badges32 silver badges78 bronze badges

answered Jan 30, 2014 at 11:52

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Siddharth Rout Over a year ago

INTERESTING READ

Collectives™ on Stack Overflow

Excel VBA Macro - Copy and insert copied cells

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related