Skip variable if not found

[ahuang3433]

Hi,

I have this pivot table where if designates to filter out specific things. Is there a way to re-write this to where it skips the variable when it's not found?

I got an error to the 2nd to last variable because that is no longer an option.

ActiveSheet.PivotTables("PivotTable2").PivotFields("Item Availability"). _
        CurrentPage = "(All)"
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Item Availability")
        .PivotItems("ETA On Time & Qty Ordered Less than Demand").Visible = False
        .PivotItems("In Stock").Visible = False
        .PivotItems("No Gating PO").Visible = False
        .PivotItems("ETA Late").Visible = False
        .PivotItems("ETA On Time").Visible = False
        .PivotItems("ETA Late & Qty Ordered Less than Demand").Visible = False
        .PivotItems("Partial qty in INV. Balance No Gating PO").Visible = False
    End With

 

[DanteAmor]

The following macro checks if the variable exists. If it exists then it hides the data. If it does not exist then the variable skips.
But also check, if all the variables are in the pivot table then send a message, since you cannot hide all the data.

For example if in your pivot table in all the items you have "In Stock", then all the rows should be hidden; and that is not possible.
It may not be a real case, but if it ever occurs, the macro already contemplates it.

Try this:

Sub FilterPT()
  Dim pt      As PivotTable
  Dim pItm    As PivotItem
  Dim aItm    As Variant
  Dim arr     As Variant
  Dim n       As Long
  
  Set pt = ActiveSheet.PivotTables("PivotTable2")
  
  arr = Array("ETA On Time & Qty Ordered Less than Demand", "In Stock", "No Gating PO", _
        "ETA Late", "ETA On Time", "ETA Late & Qty Ordered Less than Demand", _
        "Partial qty in INV. Balance No Gating PO")

  With pt.PivotFields("Item Availability")
    .ClearAllFilters
    pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
    pt.PivotCache.Refresh
    n = 0
    For Each pItm In .PivotItems
      If Not IsError(Application.Match(pItm, arr, 0)) Then
        n = n + 1
        If n < .PivotItems.Count Then
          pItm.Visible = False
        Else
          MsgBox "You are trying to hide all items"
          .ClearAllFilters
        End If
      End If
    Next
  End With
  pt.PivotCache.MissingItemsLimit = xlMissingItemsDefault
End Sub