Hi Frans,

In the PivotTable options > Data tab there is a check box for 'Refresh data when opening the file'.

Also, if your data is from an external file then you may be able to set a refresh schedule via the Data tab > Connections > Properties > Usage tab > Refresh control. This will be greyed out if it's not an option for your dataset.

Mynda

Hi Frans

As you know, PT uses a cache (an exact copy of the source data) and you need to manually "update" this cache whenever there are any changes to the source data.

There are a few ways you can do the "update/refresh".

My preference is to manually refresh the PT. Auto update/refresh can be painfully slow, especially if there are many PTs. This could be the reason why PT will not (by design) auto update. 

If you need to create a button with a macro, then you are better off clicking the Data - Refresh All button on the Ribbon.

You can also add a Worksheet_Change event macro that auto refresh the PT (or all PTs etc...) whenever data changes in the source data's worksheet. (Refer my attachment)  

Another method is to right-click the PT and choose Pivot Table Options - Data and select the Refresh data when opening the file option. This will auto-refresh the PT whenever the file is opened. You will need to do this setting for each PT. This method will not auto update when data is changed (refresh/update only upon opening the file).

Whichever method you choose will depend on your needs.

Hope this helps.

Sunny

To further add to my reply, the Refresh method assume that your data is an Excel Table.

If your data is not an Excel Table and new records are added below the range, then the Refresh method will not work. You will first need to select the PT and then from the Ribbon select PivotTable Tools - Options - Change Data Source and re-select/expand the range to include the addition.

Thanks Mynda and Sunny for your replies. Both confirm my opinion/knowledge about this. That's sometimes also good to see!

I think you're right Sunny that there is no build in option for automatically update the Pivot Table because of the possibility of many PT's and lot's of actions with every new data-entry. But in the simple cases I use it, it would be welcome for me.

I was thinking about this eventuality because I use not a data connection (only data that is updated by hand in the file itself) and don't like the ribbon or right click options (I know it's personal).

I'll try to understand the working of the macro. It looks so easy? It refreshes the PT, but I don't understand what triggers it? (I'm just starting with VBA now in the course of Jon Acampora at ExcelCampus (sorry Mynda.... ;-), so I find the code and can read it, but understanding needs some more I see.

I have my doubts about using VBA (that's a complete different topic), but I think that in this case this is a neat solution, so thank you for that!

It's nice to see the understanding, knowledge and help here on the Forum!

Hi Frans

The macro uses a Worksheet_Change event i.e. whenever there is any change on the worksheet (where the macro is located), it will auto trigger the macro.

The same macro below will work if you link it to a button. It refreshes a specific PT.

Sub UpdatePT()
        Worksheets("MyPivot").PivotTables("PivotTable1").PivotCache.Refresh
End Sub

Good luck on your VBA course 

Sunny