Pivot table item visibility

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

Hi,

I have created an application which filters data from a
Pivot Table so that the user can view only the data
relevent to them. To do this I used the following code in
a for loop:


Worksheets(mySheet).PivotTables(myPivot).PivotFields
("From Mailbox").PivotItems.Item(x).Visible = False

This works great however, I can not seem to get the items
to programatically reappear once they have been set to
Visible=False. I attempted to make them visible by
setting Visble = True but this does not work.

The pivot table design is basic in that "From Mailbox" is
the only row and their is only a count value in the data
area. I also have two fields defined as page values. How
might I make this work?
 
Set the sort to manual, and you'll be able to set visible to True.

With Worksheets(mySheet).PivotTables(myPivot) _
.PivotFields("From Mailbox")
.AutoSort xlManual, "From Mailbox"
.PivotItems.Item(x).Visible = True
End With
 
Hi
This solution works great however, I see another issue.
In my application My data changes that feeds into the
pivot table. I ran this test. I changed one of the
values in the source data (ie x to y) When I go back to
my pivot table and pull down the drop down list I still
have the option to check the value X (although it does not
exist) as well as having the new option to select my new
value of the cell which is now Y. If I run my
application the setting of Visible to True fails on the
item X (which obviously does not exist)

Is there a way in which I can refresh the items in the
dropdown of the pivot table so it accurately reflects the
changed contents of the source data?
 
Back
Top