Refreshing PivotTables

Question

I have seen a multitude of questions on the net about using VBA to refresh PivotTables. Most suggest code like this:

Sub RefreshPivotTables()
    Dim pivotTable As PivotTable
    For Each pivotTable In ActiveSheet.PivotTables
        pivotTable.RefreshTable
    Next
End Sub

While this kind of answer might give the expected result, it might do so at a terrible cost.

I have a large worksheet; 11 columns by 771427 rows. The entire worksheet is then defined as the source data to three PivotTables per column in each of three further worksheets (they perform different kinds of analysis). I have around 199 PivotTables in all.

If I use the ‘Refresh All’ button in the Data menu, then the update process takes many, many hours. With no progress report to explain the reason why, or to confirm Excel had not hung, I resorted to constructing VBA code to first list the PivotTables in a new worksheet and then, one by one, refresh the nominated PivotTable. By updating the listing as it goes, I can now see the progress being made.

However I recently stumbled on a change in the dataset that caused me to manually refresh just one of my PivotTables and to my horror all of the Pivot Tables were updated – in minutes and not in hours.

Further testing with the VBA code to skip the .RefreshTable method if the PivotTable and Worksheet names were not the one I wanted to update (see below), led me to believe that the .RefreshTable method is updating all PivotTables – either all in that worksheet, all in the workbook, or perhaps just all that share the same source data. Furthermore I believe Microsoft have the same issue when you press the ‘Refresh All’ button.

Sub RefreshPivotTables()
    Dim Pt As PivotTable
    Dim St As Worksheet
    For Each St In ActiveWorkbook.Worksheets
        For Each pt In St.PivotTables
            If pt.Name = "PivotTable151" And St.Name = "Sheet3" Then
                Set pvtTable = pt.TableRange1.PivotTable
                pvtTable.RefreshTable
            End If
        Next
    Next
End Sub

I am using Excel 2010. So what’s going on here? How can the above answer be improved to save on repeated refreshes?

One alternative can be write a small macro and instruct it to refresh only those pivot which you want to. — ManishChristian

Ross Ross · Accepted Answer · 2015-09-04T15:23:35

Maybe I am missing something too but you are telling it

For Each pivotTable In ActiveSheet.PivotTables

so of course it is going to refresh them all that are connected to that sheet.

Can we see the code you played around with to not update the specific files?

Also have you tried:

Sheets("SHEET_NAME").PivotTables("PIVOT_TABLE_NAME").PivotCache.Refresh

Source

Refreshing PivotTables

1 Answers