2

I want to programmatically refresh all the Pivot Tables in a given Worksheet of my Excel Workbook. After searching here on StackOverflow, I found this question where the answer suggests using either 

ThisWorkbook.RefreshAll

or something like

Dim pivot As PivotTable

For Each pivot In Worksheets("MySheet").PivotTables
    pivot.RefreshTable
    pivot.Update
Next

As I only want to refresh the tables in a given sheet, the first method was not what I was looking for. Hence, I modified the second method to satisfy my needs.

The question I have here is what's the difference between the RefreshTable and the Update methods of the PivotTable object? I guess if we are using both of them, it means they are different in some way.

In what situation can we use only one of them in order to save time, as it seems that refreshing and updating take a considerable amount of time when the pivot table has a lot of data?

Community
  • 1
  • 1
Oscar Anthony
  • 190
  • 3
  • 18

2 Answers2

3

According to this article and a little testing, the distinction is basically this:

Update: Update a single Pivot Table

Refresh: Update all PivotTables using the same source data

e.g. all pivot tables using the same external database, or all pivot tables using the same data table in Excel - e.g. if you have two data sheets, Data1 and Data2, refreshing any pivot table using Data1's data, will refresh all pivot tables using Data1's data

RefreshAll: Update all pivot tables in the workbook

danl
  • 430
  • 3
  • 6
  • Thanks for your answer. So basically, If I perform a `RefreshTable` on all the Pivot Tables in the sheet, there is no need to do a `Update` again? – Oscar Anthony Nov 02 '16 at 12:43
  • 1
    @OscarAnthony Yes - but also, this might trigger Pivot Tables in other sheets to refresh as well - I don't know how your Pivots are structured, but if you have Pivots in other worksheets but which get their data from the same source, these will automatically be updated when you use RefreshTable. Also, if all of your Pivots on one sheet are using the same source data then if you use RefreshTable on one of them, this will act on them all. If you have 50 tables all using the same data, using RefreshTable on every single one will actually perform 2500 Updates! - whereas RefreshAll will only do 50 – danl Nov 02 '16 at 14:27
  • 1
    but no, if you've already used RefreshTable on a table, there's definitely no need to also use Update on it as well. – danl Nov 02 '16 at 14:27
2

You omit the key line from the answer:

Or, if your Excel version is old enough,

That code is for when your Excel version is old enough (think Excel 97 ish).

Back then there was a problem when the pivot would become sort of updated after RefreshTable, but not quite, so an additional call to Update was required to "fully" update it. It's been a long time since this was fixed, I believe Excel 2003 already does not require Update after Refresh.

Community
  • 1
  • 1
GSerg
  • 76,472
  • 17
  • 159
  • 346
  • Ok now I understand. So according to @danl , if all my pivot table share the same connection, can I just refresh **one** table and assume all the others will be refreshed? – Oscar Anthony Nov 02 '16 at 12:58
  • 1
    @OscarAnthony Yes they do. When you refresh pivot cache all pivots that depend on it are also refreshed. A magical infinite refresh loop can happen if you Refresh one table and have an event handler that fires when another table refreshes (and tries to refresh the first table). I'm pretty certain I have a screwed up file where there are *different* pivot caches and yet one table refreshes when you refresh another... However if your goal is to refresh the cache, do so explicitly: `p.PivotCache.Refresh` instead of confusing the readers who will think you intended to update just one table. – GSerg Nov 02 '16 at 13:05
  • Great! so a single `PivotCache.Refresh` on a given pivot table will also refresh all the data in all the other pivot tables that share the same connection! Good to know! thanks for your help! – Oscar Anthony Nov 02 '16 at 13:19