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

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:

Libre Office Calc 5.1.4.2

edit retag close merge delete

Sort by » oldest newest most voted

Hi

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
oPilot.refresh
next oPilot
next oSheet
end sub


Of course, ToolsOptionsLibreOfficeSecurityMacros Security must be set to allow execution

Regards

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.

( 2016-11-10 17:30:42 +0200 )edit

Is there any option to solve this question without macro ?

Regards

more