Ask Your Question
0

How to duplicate last row with formulas in a macro ?

asked 2018-08-25 22:41:06 +0100

Ed38 gravatar image

updated 2020-08-05 00:06:24 +0100

Alex Kemp gravatar image

Hello,

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.

edit retag flag offensive close merge delete

Comments

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) ?

arindam gravatar imagearindam ( 2018-08-27 09:30:04 +0100 )edit

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.

Ed38 gravatar imageEd38 ( 2018-08-29 02:23:55 +0100 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2018-08-29 13:19:14 +0100

Ed38 gravatar image

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 :-)

Function UsedRange(oSheet As Variant) As Variant
Dim oCursor As Variant
    oCursor = oSheet.createCursor()
    oCursor.gotoEndOfUsedArea(False)
    oCursor.gotoStartOfUsedArea(True)
    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("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
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 com.sun.star.beans.PropertyValue
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
edit flag offensive delete link more

Comments

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

Ed38 gravatar imageEd38 ( 2018-08-29 13:25:55 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-08-25 22:41:06 +0100

Seen: 365 times

Last updated: Aug 29 '18