Register
Log In
Unanswered
Home
Dashboards
Power BI
Power Query
April 21, 2015
Offline
As I know it PivotTables don't update automatically. If I am wrong, please tell me what settings I have to use to do this.
I was thinking of 'semi' automate the update.
One way could be to place a button with an action, other way might be with a macro.
Is there another way maybe and what would be the best settings for these different ways?
Other suggestions?
July 16, 2010
Offline
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
Dashboards
June 25, 2016
Offline
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
Dashboards
June 25, 2016
Offline
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.
Dashboards
Power BI
Power Query
April 21, 2015
Offline
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!
Dashboards
June 25, 2016
Offline
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
Most Users Ever Online: 57
Currently Online: clark shao, Max Neumann
12 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Sethu nath
Jose Alvarado
James Villapudua
Sheri O'Connor
Noreen Anagnos
Connie DeMercurio
Shanna Steinberg
Holly Seamons
Mark Cooper
David Matanana
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
