Passing a variable to a pivot filter.

tbablue

Active Member
Joined
Apr 29, 2007
Messages
482
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Try just:

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

although if you've just cleared all filters, I don't know why it wouldn't already be visible.
 
Last edited:
Upvote 0
TY for your attention Rory. Most grateful.

I want to make all other PivotItems.Visible = False
 
Upvote 0
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.

Code:
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
 
Upvote 0
Hi Dante,

Really grateful for your attention.

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

Any idea?
 
Upvote 0
I made the comment at the same time I answered, we must first look for the data.

Try this

Code:
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
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top