Excel Pivot Tables Stopping Re-calc in VBA

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

Excel Pivot Tables in VBA Part 2 (Modifying Field Selections)

This uses information from Part 1 of the Pivot Table posts. It can be found here.

There are several ways to change the field selections in a Pivot Table. The two that I use most could be viewed as 1, changing the text of the selection directly, and 2, making the field multi select and selecting the fields you want. To the code (I am jumping right in so check out part 1 if you feel lost).

' Selecting everything in the field
' Current Page method
PvtFld.CurrentPage = "(All)"

' Individual Pivot Item method
For each PvtItem in PvtFld.PivotItems
PvtItem.Visible = True
Next PvtFld

Since these are not exactly clear methods, at least to me, I think a little additional explanation is needed. When using the Current Page method you can think of it as assigning a text value to the field itself, as long as the item exists within the field selections. If it doesn't it will throw an error. When using the Pivot Item Visible method you can think of this a turning the checkbox on and off for each item in the field. I'm not sure why they chose to use "Visible", but it does work . The nice thing about this is that it allows you to do a comparison between each item in the field and will not error out if the compared item doesn't match (assuming you have an 'Else' statement of some variety).

Excel Pivot Tables in VBA Part 1 (Selecting Pivot Table Parts)

Pivot tables in Excel are a life saver for many people, but moving from standard cell modification to modifying pivot table fields can be a bit more confusing. The first thing is to setup some VBA code to identify each part of the pivot table. I'm using the default names in the example below, but I always try and name everything, it makes things easier in the long run.

' Pivot Table
Dim PvtTbl as PivotTable
Set PvtTbl = Worksheets("Sheet1").PivotTables("PivotTable1")

' Pivot Field (the pivot table filters)
Dim PvtFld as PivotField
Set PvtFld = PvtTbl.PivotFields("FieldName")

' Pivot Item (the selectable items within a Pivot Field these may be optional based on the items in your field)
For each PvtItem in PvtFld.PivotItems
If PvtItem = usrInput Then
PvtItem.Visible = True
Else
PvtItem.Visible = False
End If
Next PvtFld