In this brief post, you will find how to refresh all pivot tables in LibreOffice Calc with a single click. Pivot tables don't refresh after data update.
To fresh all tables we will use a macro and event. On each saving of the document all pivot tables in the document will be refreshed.
Step 1: Create Macro which refresh all pivot tables
To start, let's create a macro which is updating pivot tables. Follow next steps to add new macro in LibreOffice Calc:
- Organize Macros
- LibreOffice Basic...
- Select module
- your Calc file, My Macros or LibreOffice Macros
- Then select submodule
- for example - Standard
- Click button New
- Enter name for the macro - i.e. PivotRefresh
- Paste this code in the newly open window:
Sub RefreshPilot dim oSheet as object, oPilot as object for each oSheet in thiscomponent.sheets for each oPilot in oSheet.DataPilotTables oPilot.refresh next oPilot next oSheet end sub
Your new macro should look like the image below:
Step 2: Assign macro to save event
Now lets add a new event which is going to execute the macro PivotRefresh. Events in LibreOffice Calc are available from:
- Tab Events
- or any other which you like - i.e. Open Document
- Assign: Macro - from the right side
- Select the macro
- Press OK
You can test the macro and the event by saving the document. On each save all pivot tables should be refreshed with the latest changes.
Step 3: Enable macro execution
By default Macros are disabled. To enable Macros in LibreOffice Calc follow next steps:
- Macro Security
- Select Medium - you will be asked every time you open document
- or Low - which is not recommended
Now you can close and reopen the document. Update data source for several pivot tables and save the document. All of them should be refreshed.