Pivot Tables - Conditional Inclusion of Fields
 

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.

Pivot Tables - Conditional Inclusion of Fields
 

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"

--
Regards,
Fred


wrote in message
oups.com...
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.

Pivot Tables - Conditional Inclusion of Fields
 

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


On Mar 20, 12:31 am, "Fred Smith" wrote:
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"

--
Regards,
Fred

wrote in message

oups.com...



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.- Hide quoted text -

- Show quoted text -