Passing a variable to a pivot filter.

[tbablue]

Hi Forum,

Some help req'd with passing a variable to a pivot filter.

How would I amend the following code such that PivotItems("99999") could be replaced by the value from a cell - a named range, [CandID_Picker]? The Pivot Table needs to filter on CandID_Picker - and nothing else.

With Activesheet.PivotTables("CandID_Picker_table").Pivotfields("CandID")
.ClearAllFilters
.PivotItems("99999").Visible = True
End With

Any help appreciated.

 

[RoryA]

Try just:

.PivotItems("" & [CandID_Picker]).Visible = True

although if you've just cleared all filters, I don't know why it wouldn't already be visible.

 

[tbablue]

TY for your attention Rory. Most grateful.

I want to make all other PivotItems.Visible = False

 

[DanteAmor]

Try this

If the data to be filtered does not exist in the pivot table, an error is generated, because you must select at least one data.
With the following check if the data does not exist, then sends the message "No match".

Or maybe you can verify the data in the original database, before filtering.

Sub Filter_Pivot()
  Dim pi As PivotItem, n As Long
  
  Application.ScreenUpdating = False
  n = 0
  With ActiveSheet.PivotTables("CandID_Picker_table").PivotFields("CandID")
    .ClearAllFilters
    For Each pi In .PivotItems
      If LCase(pi) <> LCase([CandID_Picker]) Then
        n = n + 1
        If n < .PivotItems.Count Then
          pi.Visible = False
        Else
          MsgBox "No match"
          .ClearAllFilters
        End If
      End If
    Next
  End With
End Sub

 

[tbablue]

Hi Dante,

Really grateful for your attention.

The code fails at pi.Visible = False - I can't immediately see why.

Any idea?

 

[DanteAmor]

I made the comment at the same time I answered, we must first look for the data.

Try this

Sub Filter_Pivot()
  Dim pi As PivotItem, f As Range
  Application.ScreenUpdating = False
  With ActiveSheet.PivotTables("CandID_Picker_table").PivotFields("CandID")
    .ClearAllFilters
    Set f = Range(.DataRange.Address).Find([CandID_Picker], , xlValues, xlWhole)
    If f Is Nothing Then
      MsgBox "No match"
    Else
        For Each pi In .PivotItems
          If LCase(pi) <> LCase([CandID_Picker]) Then pi.Visible = False
        Next
    End If
  End With
End Sub

 

[tbablue]

Really grateful for your attention. Problem resolved.

Thank you!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.