VBA Programming with Pivot Tables

  • Thread starter Thread starter no1uknow
  • Start date Start date
N

no1uknow

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!!

(e-mail address removed)
 
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
 
Back
Top