VBA - Copy a row and insert into row under it

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.

2 Answers

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.

Select cell from which you want to start copying and run this macro

Dim myCell
Set myCell = ActiveCell
While ActiveCell.Value <> "" Rows(ActiveCell.Row).Select Selection.Copy Selection.Insert Shift:=xlDown myCell.Offset(1, 0).Select Set myCell = ActiveCell
Wend