VBA Programming with Pivot Tables
 

Hello Excel Users!!

I am developing a macro which uses pivot tables to gather
data. Several times throughtout the programming I
am "filtering" the data by limiting the pivot table
fields. When writing the VBA for this I am using:

With ActiveSheet.PivotTables("PivotTable1").PivotFields
("xxxxxx")
..PivotItems("xxxxx").Visible = False
..PivotItems("xxxxx").Visible = True
End With

1. Is there anyway not to have to list all the items I
want Visible or not Visible?

2. Also the list is not always constant yet the items I
want "Filtered" is. Is there a way to write the
programming just to list the items I want visible?

Thanks for any help you can offer!!

VBA Programming with Pivot Tables
 

No1,

This might help.

You can replace the name of the pivotfield with an index
number. Remember to leave at least one field showing at
all times or you will generate an error. Indexing will
work for all of the collections in a pivot table.

for i = 1 to x
ActiveSheet.PivotTables("PivotTable1").PivotFields (i)_
.visible = false
next i

Regards,
Mike




-----Original Message-----
Hello Excel Users!!

I am developing a macro which uses pivot tables to
gather
data. Several times throughtout the programming I
am "filtering" the data by limiting the pivot table
fields. When writing the VBA for this I am using:

With ActiveSheet.PivotTables("PivotTable1").PivotFields
("xxxxxx")
..PivotItems("xxxxx").Visible = False
..PivotItems("xxxxx").Visible = True
End With

1. Is there anyway not to have to list all the items I
want Visible or not Visible?

2. Also the list is not always constant yet the items I
want "Filtered" is. Is there a way to write the
programming just to list the items I want visible?

Thanks for any help you can offer!!


.