Prevent Excel from refreshing Pivot Table until told to do so by VBA

excel pivot-table excel-template vba
David Gard picture David Gard · Sep 28, 2012 · Viewed 24.3k times · Source

I have an Excel Template (.xlt) that contains a Worksheet for report data, and 4x Worksheets with Pivot Tables.

When our reporting system creates a report and applys the template to it, some of the fields referenced by the Pivot Tables don't exist, as a Macro is run to create them as part of the process of the reporting system.

However, bescuse these fields do not exist, Excel is throwing up an error (Error occurred while accessing component property/method: REFRESH. Reference is not valid.). To try and prevent this, I have unset the option on all Pivot Tables for Refresh data when opening the file.

I know that I can refresh the Pivot Tables using VBA, but is there a way to prevent the Pivot Tables refreshing themsleves when the documnet is opened, and instead refresh them through VBA, after the new fields have been created? Thanks.

Function UpdatePivots()
    ActiveWorkbook.Sheets("DJG Marketing - Client List by ").PivotTables("PivotTable1").PivotCache.Refresh
    ActiveWorkbook.Sheets("DJG Marketing - Client List by ").PivotTables("PivotTable2").PivotCache.Refresh
    ActiveWorkbook.Sheets("DJG Marketing - Client List by ").PivotTables("PivotTable3").PivotCache.Refresh
    ActiveWorkbook.Sheets("DJG Marketing - Client List by ").PivotTables("PivotTable4").PivotCache.Refresh
End Function

Answer

Alex Gordon picture Alex Gordon · Sep 28, 2012

here's what you need:

' Disable automatic calculation
Application.Calculation = xlCalculationManual
' do regular operation here
' Force a calculation
Application.Calculate
' Then remember to run automatic calculations back on
Application.Calculation = xlCalculationAutomatic

taken from: http://www.zerrtech.com/content/stop-vba-automatic-calculation-using-applicationcalculation-manual

Related questions

Simple Pivot Table to Count Unique Values

This seems like a simple Pivot Table to learn with. I would like to do a count of unique values for a particular value I'm grouping on. For instance, I have this: ABC 123 ABC 123 ABC 123 DEF 456 DEF 567 DEF 456 DEF 456 What …

Read More
PivotTable to show values, not sum of values

I'm wanting to display a pivot table and for it to show me the actual values, one on each row, rather than a sum of the values. E.g. Name Jan Feb Mar Apr Bob 12 10 4 3 5 James 2 6 8 1 15 etc. My starting point …

Read More
Filter Excel pivot table using VBA

I have tried copying and pasting solutions from the internet forever now to try to filter a pivot table in Excel using VBA. The code below doesn't work. Sub FilterPivotTable() Application.ScreenUpdating = False ActiveSheet.PivotTables("PivotTable2").ManualUpdate = True ActiveSheet.PivotTables("…

Read More