Turning pivot item filters on and off

[Orfil]

The code cycles through the three pivot tables (ptFeedbackGood, ptFeedbackAverage, ptFeedbackBad) and, depending on which button is clicked, loads the values for either Quality, Durability, Value for Money or Safety.

Each time a button is clicked, the data, column and row values in each of the three pivot tables is deleted and replaced.

The method I have adopted works perfectly well for pivot fields. The question is, how to delete and replace the Good, Average and Bad pivot items? If I turn them all off, the data values cease to exist.

There is a loop and a variable that cycles through the Good Bad Average array. My best guess is a way of telling the pivot field to make visible whatever the current value of the pivot item is while making not-visible the others.

Help in this regard would be greatly appreciated

 

[NARAYANK991]

Hi ,

I am not sure what you want to do , but will this help ?

            With pvt.PivotFields("Quality")
'                .PivotItems(svr).Visible = True
       
                For Each pi In .PivotItems
                    If pi.Name = fbr Then pi.Visible = True Else pi.Visible = False
                Next pi
           
            End With

Narayan

 

[jeffreyweir]

I haven't looked at your file, but I've got a comment on @NARAYANK991's code above.

When iterating through the PivotItems collection, you should first set the PivotTable's .ManualUpdate property to TRUE before your PivotItems loop, and set it back to FALSE again when you are done. Otherwise the PivotTable will refresh after each and every PivotItem change. And that can add minutes of unnecessary overhead.

You should also first check whether the PivotItem.Visible status actually needs to change, because it takes far longer to change it than to check it. I have a post on this at http://dailydoseofexcel.com/archives/2013/11/14/filtering-pivots-based-on-external-ranges/

 

[NARAYANK991]

Hi Jeff ,

Thanks for the tips.

Regarding your second tip , since only one pivotitem is being changed , should it matter ?

Narayan

 

[NARAYANK991]

Hi ,

Going through your code once more , I find that the variable pvt is never assigned a value ; is this an oversight ?

If so , change your starting code as follows :

'  Array loop start
    For i = 0 To UBound(FeedbackPvts)
       Set pvt = ThisWorkbook.Worksheets("Dashboard").PivotTables(FeedbackPvts(i))
       With pvt
            .ManualUpdate = True

Narayan

 

[Orfil]

Hi ,

Going through your code once more , I find that the variable pvt is never assigned a value ; is this an oversight ?

If so , change your starting code as follows :

'  Array loop start
    For i = 0 To UBound(FeedbackPvts)
       Set pvt = ThisWorkbook.Worksheets("Dashboard").PivotTables(FeedbackPvts(i))
       With pvt
            .ManualUpdate = True

Narayan

Hi, Narayan

I think that the way I've laid out the code and the redundancies probably misled you. There is actually a SET PVT in there....

            Set pvt = Sheets("Dashboard").PivotTables(fpt)
            FeedbackFld = pvt.PivotFields("Quality")

That FeedbackFld in the second line is a relic of the various approaches I've taken since it serves no purpose here

 

[Orfil]

I haven't looked at your file, but I've got a comment on @NARAYANK991's code above.

When iterating through the PivotItems collection, you should first set the PivotTable's .ManualUpdate property to TRUE before your PivotItems loop, and set it back to FALSE again when you are done. Otherwise the PivotTable will refresh after each and every PivotItem change. And that can add minutes of unnecessary overhead.

You should also first check whether the PivotItem.Visible status actually needs to change, because it takes far longer to change it than to check it. I have a post on this at http://dailydoseofexcel.com/archives/2013/11/14/filtering-pivots-based-on-external-ranges/

Thank you, Jeffrey, that's very good advice, particularly in my current circumstances. The dashboard I'm building uses pivot tables and charts tied to pivot table, so very useful. Thank you.

 

[Orfil]

Hi ,

I am not sure what you want to do , but will this help ?

            With pvt.PivotFields("Quality")
'                .PivotItems(svr).Visible = True
      
                For Each pi In .PivotItems
                    If pi.Name = fbr Then pi.Visible = True Else pi.Visible = False
                Next pi
          
            End With

Narayan

Hi, Narayan

This is exactly what I was looking for. I'll test it fully and get back to you.

 

[Orfil]

Hi ,

I am not sure what you want to do , but will this help ?

            With pvt.PivotFields("Quality")
'                .PivotItems(svr).Visible = True
      
                For Each pi In .PivotItems
                    If pi.Name = fbr Then pi.Visible = True Else pi.Visible = False
                Next pi
          
            End With

Narayan

Works perfectly. Thank you, Narayan