Ask Your Question
0

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

asked 2019-01-14 16:06:26 +0100

shodge gravatar image

updated 2019-01-14 20:58:47 +0100

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 flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2019-01-14 20:53:49 +0100

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 Sort_data_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. Sort_data_2

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. Sort_data_3

edit flag offensive delete link more

Comments

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.

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

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

SM_Riga gravatar imageSM_Riga ( 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.

shodge gravatar imageshodge ( 2019-01-14 21:23:37 +0100 )edit
0

answered 2019-01-14 18:20:18 +0100

shodge gravatar image

updated 2019-01-14 18:21:21 +0100

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
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-01-14 16:06:26 +0100

Seen: 22 times

Last updated: 2 days ago