Excel – VBA – Copy a row and insert into row under it

microsoft excelvba

Prefacing by saying I don't have any VBA experience, but I need this data sorted fairly quickly. I have a Sage 300 Invoice Import I need to do. I can sort this data via formulas after it's made a second time. The root of the problem is that this data needs to actually be put into two rows per one row, but excel skips lines.

I have a partial snippet of code that would work for me, but I get an outside reference error when I run it.

ActiveCell.EntireRow.Select
Selection.Copy
Selection.Insert Shift:=xlDown

Any tips or hints? I would love it if it would just copy that row to the one below it exactly, until it hits an empty row.

I read the sort comment and it dawned on me that I could recopy the data and sort by a unique value so that it all came in as it was supposed to – but I'd like to know the VBA code anyway.

Best Answer

Sub copyRowToBelow()
    Dim rng As Range
    Set rng = Range("A1") ' <~~  Change this

    Do While (rng.Value <> "")
        ' Insert a row below the current one
        rng.Offset(1).Insert

        ' Copy the current row and paste it into the row we just inserted
        rng.EntireRow.Copy rng.Offset(1)

        ' Set the range declaration for 2 rows below the current one
        Set rng = rng.Offset(2)
    Loop
End Sub

The marked line ("Change this") can be declared in the code, or you can swap it for Set rng = ActiveCell if you want it to run off of the cell the user is working on at the time of running the macro.

Comments are not necessary for the code, they are there purely to help you learn more about VBA.

Related Question