CODEWITHSUNDEEP
libreoffice-calclibreoffice-basic
Tobi S
Tobi S

Reputation: 161

How to get data records from a table into a result table and is displayed as summary with a total sum?

I am having a table called "Table", in which there is a list of items with prices - when pressing a button, I would like to transfer all data to another result Table in the same workbook, where you get listed all items from the database and the items from the list and gives out the difference of income and costs as a =Sum Function

It works just fine in Excel, but I would like to have a macro for Libre office calc so I can do the same in Libre Office too.

Hint: I uploaded 2 screenshots of the as-is state and the target state
If you need further code, I could edit my post for you

Table with data to be viewed in another result table
Table with data to be viewed in another result table

Result table
Result table

Upvotes: 0

Views: 335

Answers (1)

TorbenIT
TorbenIT

Reputation: 292

This should help ya:

    
    Sheets("Matrix").Select
    Range("C3").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIF(Aufstellung!R13C2:R[997]C;Matrix!RC[-1];Aufstellung!R13C3:R[997]C)"
    Range("C3").Select
    Selection.AutoFill Destination:=Range("C3:C42"), Type:=xlFillDefault
    Range("C3:C42").Select
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 8
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    Range("D3").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIF(Aufstellung!R13C2:R[997]C;Matrix!RC[-2];Aufstellung!R13C4:R[997]C)"
    Range("D3").Select
    Selection.AutoFill Destination:=Range("D3:D42"), Type:=xlFillDefault
    Range("D3:D42").Select
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 8
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    Range("B3:D42").Select
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 8
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    Selection.Copy
    Range("F3:H42").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("I3:I42").Select
    Application.CutCopyMode = False
    
'    ActiveWorkbook.Worksheets("Matrix").Sort.SortFields.Clear
'    ActiveWorkbook.Worksheets("Matrix").Sort.SortFields.Add Key:=Range("I3"), _
'        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
'    With ActiveWorkbook.Worksheets("Matrix").Sort
'        .SetRange Range("F3:I42")
'        .Header = xlNo
'        .MatchCase = False
'        .Orientation = xlTopToBottom
'        .SortMethod = xlPinYin
'        .Apply
'    End With
    Call SortMatrix_FI()
    Range("F3:I42").Select
    Selection.Copy
    Sheets("Kassenblatt").Select
    Range("C6:F45").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B6").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=IF(RC[4]>0;R[-1]C+1;"""")"
    Range("B6").Select
    Selection.AutoFill Destination:=Range("B6:B45"), Type:=xlFillDefault
    Range("B6:B45").Select
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 11
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 8
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    Range("A6").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[5]>0;R1C4;"""")"
    Range("A6").Select
    Selection.AutoFill Destination:=Range("A6:A45"), Type:=xlFillDefault
    Range("A6:A45").Select
    ActiveWindow.ScrollRow = 15
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 11
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 8
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    Range("I5").Select```

Upvotes: 1

Related Questions