Pivot Tables - Conditional Inclusion of Fields

[kansaskannan]

I have a table with 50 fields and 10,000 records. I have written VBA
code that generats about 100 pivot tables. Each pivot table has 5 to
9 column fields out of the 50. Some of the pivot tables include only
those records where the fields satisfy particular conditions (e.g.,
pivot of product sales where State = Arizona, AND Year = 2000). I am
presently doing this by adding additional columns, based on the
conditions (e.g., AZ2000=YES), and then creating pivots from the
'added-on' columns. As there are very many conditions to be met, I do
not want to include each column in the pivot table itself. However,
this has resulted in the number of columns being over 150. I am
having problems keeping track of the conditions, and debugging the
code.

I would like to be able to create the pivot tables directly from the
original data incorporating VBA code for conditional inclusion of
fields. Any suggestions? Thank you for your help.

 

[Fred Smith]

Yes. Forget the extra columns, use Page Fields.

If you want date where State =AZ, set state as a page field, then select AZ.
Your code would look something like:

..PivotFields("State").Orientation = xlPageField
..PivotFields("State").CurrentPage = "AZ"

 

[kansaskannan]

Thank you, Fred, that should do the trick!
Have a good day.