I'm not sure why this was so hard to find, but once I did it seemed obvious. Excel refreshes a pivot table after each change. This can become very time consuming if you are changing multiple field settings. The below code modifies the "ManualUpdate" property of a pivot table so any changes you make will not be calculated until you either turn off manual updating or force a refresh.
Dim ws As Worksheet
Dim PvtTbl As PivotTable
Set ws = ActiveWorkbook.ActiveSheet
Set PvtTbl = ws.PivotTables("MyPivotTable")
'Turn on manual updating
PvtTbl.ManualUpdate = True
' Enter your pivot table code here
' If you need to refresh the pivot table within this block
PvtTbl.RefreshTable
'Turn off manual updating
PvtTbl.ManualUpdate = False
No comments:
Post a Comment