Thread: Changing filter in a pivot

Hi,

Cannot solve one problem. I've got a big pivot table, with a couple of fields in the "Filter" section, let's say "Product" and "Segment". There is a dropdown box with several items. Picking an item should change the values in one or both fields in "Filter" section in a certain way. I've made the following code:

Code:

With ActiveSheet.PivotTables("PT").PivotFields("Product")
        .PivotItems("Product1").Visible = False
        .PivotItems("Product2").Visible = True
        .PivotItems("Product3").Visible = False
        .PivotItems("Product4").Visible = True

(and so on with "Segment")

The problem is that in several seconds (since the pivot is big!), it changes its appearance several times before the eyes of a confused user. I.e. every change in .Visible property from false to true and vice versa makes the pivot to render itself to next interim state. May I somehow make the pivot change appearance only ONCE - from initial state to the very final state?

Thanks a lot for your help!

What if you disable screen updating before doing your code?

Code:

Application.Screenupdating = false
With ActiveSheet.PivotTables("PT").PivotFields("Product")
        .PivotItems("Product1").Visible = False
        .PivotItems("Product2").Visible = True
        .PivotItems("Product3").Visible = False
        .PivotItems("Product4").Visible = True

(and so on with "Segment")

Application.Screenupdating = true

It worked.
Thanks a lot!