I’m using Calc 25.2.2.2 on Windows 11.
I’m trying to write a basic macro that should actually copy all formulas from one row to a different row, but only the formulas. Additionally the relative references in the formulas need to be updated.
The manual workflow is: select source row, copy, select first cell of target row, paste only formulas.
This manual workflow gives the wanted result.
I have already tried a lot of different macro options using the API or the dispatcher. Just copying the formula property from one cell to the other won’t update the references.
Actually I thought this should work:
Sub InsertRow
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
oSheet=thiscomponent.getcurrentcontroller.activesheet
oSel = ThisComponent.getCurrentSelection()
nRow = oSel.CellAddress.Row
If nRow = 0 Then Exit Sub
' Insert new row below
oSheet.Rows.insertByIndex(nRow + 1, 1)
' Copy formulas
Dim oSource As Object, oTarget As Object
oSource = oSheet.getCellRangeByPosition(0, 1, oSheet.Columns.Count-1, 1) ' source row
oTarget = oSheet.getCellByPosition(0, nRow+1) ' target cell
ThisComponent.CurrentController.Select(oSource)
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
ThisComponent.CurrentController.Select(oTarget)
dispatcher.executeDispatch(document, ".uno:PasteOnlyFormula", "", 0, Array())
End Sub
It seems that there is a problem with the Ranges. Unlike as the manual workflow the above macro does only paste into the selected cell.
When I adjust the sorce and target to be like:
oSource = oSheet.getCellRangeByPosition(0, 1, oSheet.Columns.Count-1, 1) ' source row
oTarget = oSheet.getCellRangeByPosition(0, nRow+1, oSheet.Columns.Count-1, nRow+1) ' target row
it oddly copies the formula of the first column into all cells of the target row.
I also tried to copy the formulas cell by cell with a for loop, but even here the result is that always only the formula of the first cell is pasted in all cells, so it seems that although I can see that different source cells are selected for copying, actually only the very first copy does happen.
To completely mess up my mind all of this only is working for a few runs and then it stops working completely and nothing is copied or pasted any more.
Any help is appreciated.