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:

  • Tools
  • Macros
  • Organize Macros
  • LibreOffice Basic...
  • Organizer
  • 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
  • Edit
  • 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:

refresh-all-pivot-tables-libreoffice-calc

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:

  • Tools
  • Customize...
  • Tab Events
  • Select Save Document
    • 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:

  • Tools
  • Options
  • LibreOffice
  • Security
  • 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.

Happy pivoting!