How to duplicate last row with formulas in a macro?


I’d like to create a button for adding rows at the bottom of a table but I have no idea how to make the macro.

In column “Sums” ($B), I need to find the last/lowest non empty cell and copy its formula into the next lower one.
There is nothing to be copied in others columns.

Items       Sums   Product1  Product2  Product3
Item 1       3         1         1        1
Item 2       1                   1
Item 3       2         1                  1

Can anybody help me ?
Thanks in advance.

Your question is not clear. Are you saying that you want to copy the formula of “1” in Sums column (which is lowest) and copy it to “2” (2nd lowest) ?

Sorry, my prior example only shows results after calculation.
Second column “Sums” contains the sum of “Product1 + Product2 + Product3” ; formula is relative and can be copied downward for adding new rows.
So, I just need to duplicate second cell (Sum) of the latest item to create a new row for a new item and manually fill other cells.

I’ve finally found a solution inspired from an answer to an other question mixed with a recorded macro.
Probably ugly for programmers but it works (and it’s hidden under the button :slight_smile:

Function UsedRange(oSheet As Variant) As Variant
Dim oCursor As Variant
    oCursor = oSheet.createCursor()
    UsedRange = oCursor
End Function

Function LastRow(oRange As Variant) As Long 
    LastRow = oRange.getRangeAddress().EndRow
End Function

sub AddRow
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
Dim oRange As Variant, sMessage As String 
oRange = UsedRange(ThisComponent.getCurrentController().getActiveSheet())

rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("")

rem ----------------------------------------------------------------------
dim args1(0) as new
args1(0).Name = "ToPoint"
args1(0).Value = "$B"+ CStr(LastRow(oRange)+1)

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args3(0) as new
args3(0).Name = "ToPoint"
args3(0).Value = "$B" + CStr(LastRow(oRange) + 2)

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args3())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())

end sub

Sorry, I have no enough points to mark my question as solved by my own answer.