Page 1 of 1
[Solved] Duplicating formulas automatically upon row insert
Posted: Wed May 01, 2019 10:45 pm
by CFoertmeyer
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
Re: Duplicating formulas automatically upon a row insert
Posted: Wed May 01, 2019 11:17 pm
by RusselB
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.
Re: Solved Duplicating formulas automatically upon a row ins
Posted: Wed May 01, 2019 11:30 pm
by CFoertmeyer
Thank you!
Re: Solved Duplicating formulas automatically upon a row ins
Posted: Wed May 01, 2019 11:52 pm
by Lupp
You may check this very raw code for usability:
Code: Select all
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
Sub clearExceptFormulasAndFormatting()
sheet = ThisComponent.CurrentController.ActiveSheet
pastedRg = sheet.getCellRangeByName("$A$3:$AMJ$3")
pastedRg.clearContents(143)
End Sub
Re: Solved Duplicating formulas automatically upon a row ins
Posted: Thu May 02, 2019 12:19 am
by Villeroy