Search code examples
excelvbainsertcopyrow

vba to copy a row and insert to row below and then repeat

I would like to copy and insert a row, ie row 10 and insert to row 11. I would then like to run that code again (via a command button), to copy row 11 and insert to row 12. I would like to be able to repeat this indefinitely, ie row 12 to row 13 and so on.

I used the record macro function to copy and insert, but it just copies row 10 and inserts to row 11.

Application.CutCopyMode = False

With Worksheets("SUMMARY")

Rows("11:11").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Rows("10:10").Select
Selection.AutoFill Destination:=Rows("10:11"), Type:=xlFillDefault
Rows("10:11").Select
Solution

  • Use the below snippet

    Sub CopyAndInsertRow() Dim rowToCopy As Long Dim insertRow As Long

    rowToCopy = 10 ' Specify the row number to copy
insertRow = 11 ' Specify the row number to insert

Rows(rowToCopy).Copy
Rows(insertRow).Insert Shift:=xlDown

    End Sub