We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

automatic update/refresh of all pivot tables at file opening: how?

asked 2016-11-07 12:28:21 +0200

Wololo gravatar image

updated 2016-11-10 14:01:28 +0200

Hi, I have one LO calc file with severall sheets:

  1. one sheet with my "transactions" : a few columns and many rows. The data is "referenced" within a named range to be used in several pivot tables, so that the pivots have no issues if i add new records.
  2. 16 other sheets having different pivot tables, all analysing data of the same unique named range from point 1.

I would like that all these pivot tables automatically refresh when I open the file, so that I don't have to go manually in each single pivot table to refresh the data (Right-click > Refresh). in MS Excel 2007 I can tick a check box in each single pivot table properties for this auto-update at file opening.

See this screenshot from Excel which I would like to find in LibreOffice:

image description

Has anyone any advices? Thanks in advance

Libre Office Calc

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2020-01-30 22:47:21 +0200

The Sheet toolbar menu has a Sheet Events item. The macro to refresh mentioned already could be added to the Activate event. I have not tried it but would be worth exploring especially say if you also want to have a cell with a timestamp of last refresh.

Another issue is when to refresh. I have a large collection of data worksheets with each consolidating to a single row. Another worksheet collates the total rows of each worksheet by using a series of indirect statements with sheet name as a variable. My pivot table is on the collated worksheet. A minor edit to any of the data worksheets could affect the pivot.

My solution was to have a cell above the pivot which compared the number of rows and total dollars from my consolidation tag with the pivot. The cell value was "Please refresh" on mismatch with conditional formatting changing colour, etc.

edit flag offensive delete link more

answered 2017-08-27 22:55:42 +0200

Wololo gravatar image

thank you @https://ask.libreoffice.org/en/users/15459/pierre-yves-samyn/

Is there any option to solve this question without macro ?


edit flag offensive delete link more

answered 2016-11-10 15:08:51 +0200

pierre-yves samyn gravatar image


With ToolsCustomizeEvents tab, you can assign a macro to the Open Document event:

Sub RefreshPilot
dim oSheet as object, oPilot as object

for each oSheet in thiscomponent.sheets
    for each oPilot in oSheet.DataPilotTables
    next oPilot
next oSheet
end sub

Of course, ToolsOptionsLibreOfficeSecurityMacros Security must be set to allow execution


edit flag offensive delete link more


You might also add the macro to the toolbar so you can easily refresh all the pivot tables after you have entered new data instead of closing and re-opening the document. Use Tools, Customise..., select the Toolbar tab, select save in the document instead of save in Calc as you only need to use this for the document that has pivot table. Scroll to the end of the list of commands, click on Add, in category under LibreOffice Macros, find the macro and then click on Add.

mark_t gravatar imagemark_t ( 2016-11-10 17:30:42 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-11-07 12:28:21 +0200

Seen: 3,139 times

Last updated: Jan 30 '20