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).