# How to copy Row 1 and Paste Row 2,3 loop till end?

EDIT: I posted a working code in the answers, but not allowed to select it due to age of account or something.

I have 300 lines of data here that need 2 rows inserted after each line and the line duplicated to those rows.

I created a macro to add those 2 lines and have been manually copy pasting... but it takes forever.

How can I macro the copy paste?

Copy r1

Paste r2,3

Copy r4

Paste r5,6

copy r7

Paste r8,9

go to I=50 or end document... I am happy to change the I number to make things simpler for me to understand and write.

This is my current macro, BUT I am happy to use 2 separate macros to accomplish this.

sub Insert_rows_skipping
dim document   as Object
dim dispatcher as object

document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "By2"
args2(0).Value = 2
dim args3(1) as new com.sun.star.beans.PropertyValue
args3(0).Name = "By"
args3(0).Value = 1
For i = 1 To 124   ' This should be adjusted according to need.
dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args3())
dispatcher.executeDispatch(document, ".uno:InsertRowsBefore", "", 0, Array())
args3(0).Value = 2
dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args2())
dispatcher.executeDispatch(document, ".uno:InsertRowsBefore", "", 0, Array())
args2(0).Value = 2

Next i
end sub

edit retag close merge delete

Sort by » oldest newest most voted

Hello @shodge

You can achieve goal easily without any coding.

Step 1. Add helper column before data range and fill it with serial numbers/ID starting from 1

Step 2. Copy all rows containing data and paste it below the range. Repeat operation as many times, as copy of each row needed. In your case you need to paste range 2 times.

Step 3. Click on the header of ID column and go to Data-> Sort Ascending The range will be sorted in the sequence wanted. Delete helper column if needed.

more

Interesting take on the problem. I see how this would work but found it much easier to just click run macro. The help is appreciated.

( 2019-01-14 20:57:17 +0100 )edit

The idea would be the same in code also. I will add info a bit later

( 2019-01-14 21:03:46 +0100 )edit

No worries, I posted a working code below. Depending on size of the data, your idea might be faster.

( 2019-01-14 21:23:37 +0100 )edit

I got this to work! ... works with manual value on the i. If anyone knows how to detect end of row... that would be great.

sub Insert_rows_skipping
dim document   as Object
dim dispatcher as object

document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
For i = 1 To 3   ' This should be adjusted according to need.
dispatcher.executeDispatch(document, ".uno:SelectRow", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:InsertRowsAfter", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:InsertRowsAfter", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:SelectRow", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:SelectRow", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, Array())
Next i
end sub

more