Ask Your Question

How can I create Pivot Table with Macro in LibreOffice Calc

asked 2018-12-15 21:28:21 +0100

libremacrouser gravatar image

updated 2020-07-11 20:57:19 +0100

Alex Kemp gravatar image


I have work with LibreOffice Macro.

I want to create pivot table with macro, because these data will change often. If I make it with macro, they will be otomatically.

I added a sample about my table, my first goal it to collect B Code. B code must be uniqe in my pivot table, and I want to collect G1,G2,G3 column if B Code is the same.

I added an example about my pivot table

How can I write it with Basic Code, Can you help me about it?

C:\fakepath\Example about Pivot Table.ods

edit retag flag offensive close merge delete


Hi @JohnSUN. Do you have any suggestion for it?

libremacrouser gravatar imagelibremacrouser ( 2018-12-15 21:35:45 +0100 )edit

It is not difficult. How big is your real work table?

JohnSUN gravatar imageJohnSUN ( 2018-12-15 22:09:21 +0100 )edit

2 Answers

Sort by » oldest newest most voted

answered 2018-12-15 22:48:06 +0100

JohnSUN gravatar image

For not very large amounts of data, this solution will be fast enough - C:\fakepath\pseudoPivot.ods

edit flag offensive delete link more


I'm irritated now. The OP talked of a pivot table and of frequent need to update it. Well, the respective sheet in his example document doesn't contain a pivot table, but it shows results you can get with a pivot table using SUM as the accumulating function. If the table once is created the standard way, you only need to identify it as an object and to trigger it's .refresh method when needed. Why do all the work by user code?

Lupp gravatar imageLupp ( 2018-12-15 23:17:18 +0100 )edit

Don't worry, colleague, just keep in mind that the OP can confuse the terms and write a "Pivot Table" when it means "Subtotals" Examining the attached sample data allows to understand the cause of the confusion

JohnSUN gravatar imageJohnSUN ( 2018-12-16 07:06:12 +0100 )edit

answered 2018-12-16 00:08:31 +0100

Lupp gravatar image

Of course, you can also create a PivotTable (DataPilotTable) by user code, but I would assume it useless to map all the settings you can make in the big PivotTable dialogue to parameters. Anyway it's always a special task to pass parameters to a Sub. I also would not try to mimic the functionality of a PivotTable by user code. The very next day expectations may change a bit, and the code will need its own refresh.

As I saw the OP the accent was on frequent or even automatic refresh.
How I would do it is demonstrated in this attachment.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-12-15 21:28:21 +0100

Seen: 224 times

Last updated: Dec 16 '18