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...
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
Maya is a hardware enthusiast who tests and reviews smart home devices, smartphones, wearables, and audio gear. She focuses on practical consumer value and build quality.
