[Solved] Duplicating formulas automatically upon row insert

Discuss the spreadsheet application

[Solved] Duplicating formulas automatically upon row insert

Postby CFoertmeyer » Wed May 01, 2019 10:45 pm

I am trying to find a way, if at all possible, in the spreadsheet, to duplicate the formulas I have in row 1 into an inserted row which will now become row 2. Since my formulas aren't absolute, I was hoping functional formulas could populate themselves into the new row 2 as the inserted row is created. Anyone know an answer to this?

Chuck
Last edited by CFoertmeyer on Wed May 01, 2019 11:30 pm, edited 1 time in total.
Open Office 4.1.6 Windows 10 C.H. Fooertmeyer
CFoertmeyer
 
Posts: 2
Joined: Wed May 01, 2019 8:03 pm

Re: Duplicating formulas automatically upon a row insert

Postby RusselB » Wed May 01, 2019 11:17 pm

Welcome to the Forums.
Calc does not have the ability to do this built in. You might be able to write a macro to do this.
The easiest way is to insert the row(s), then highlight a row that has the formulas, press Ctrl+C (to copy the row), then select the cell(s) in column A of the rows that you inserted, next press Ctrl+V (to paste) the information that you previously copied into the rows that you inserted.
OpenOffice 4.1.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5300
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Solved Duplicating formulas automatically upon a row ins

Postby CFoertmeyer » Wed May 01, 2019 11:30 pm

Thank you!
Open Office 4.1.6 Windows 10 C.H. Fooertmeyer
CFoertmeyer
 
Posts: 2
Joined: Wed May 01, 2019 8:03 pm

Re: Solved Duplicating formulas automatically upon a row ins

Postby Lupp » Wed May 01, 2019 11:52 pm

You may check this very raw code for usability:
Code: Select all   Expand viewCollapse view
Sub demo()
Dim dispPr   As Object
Dim dispHe   As Object
dispPr          = ThisComponent.CurrentController.Frame
dispHe          = createUnoService("com.sun.star.frame.DispatchHelper")
Dim argsTo(0) As New com.sun.star.beans.PropertyValue
argsTo(0).Name  = "ToPoint"
argsTo(0).Value = "$A$3"
dispHe.executeDispatch(dispPr, ".uno:GoToCell", "", 0, argsTo())
dispHe.executeDispatch(dispPr, ".uno:InsertRowsBefore", "", 0, Array())
argsTo(0).Value = "$A$2:$AMJ$2"
dispHe.executeDispatch(dispPr, ".uno:GoToCell", "", 0, argsTo())
dispHe.executeDispatch(dispPr, ".uno:Copy", "", 0, Array())
dim args3(0) as new com.sun.star.beans.PropertyValue
argsTo(0).Value = "$A$3"
dispHe.executeDispatch(dispPr, ".uno:GoToCell", "", 0, argsTo())
dispHe.executeDispatch(dispPr, ".uno:Paste", "", 0, Array())
End Sub
If there were also pasted contents not being formulas you may want to clear these contents from the newly inserted row abd to keep the formulas only:
Code: Select all   Expand viewCollapse view
Sub clearExceptFormulasAndFormatting()
sheet = ThisComponent.CurrentController.ActiveSheet
pastedRg = sheet.getCellRangeByName("$A$3:$AMJ$3")
pastedRg.clearContents(143)
End Sub
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2523
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Solved Duplicating formulas automatically upon a row ins

Postby Villeroy » Thu May 02, 2019 12:19 am

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26980
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 17 guests