Filtering Pivot Table Based on a Cell Value

[mkilavuz]

Dear Mr. Excel Forum Member,

I would like to use a VBA script to filter a pivot table linked to a cell value. I found below code online and try to replicate the same example that comes with this code. However, I end up with having "Run-time error 1004" which says "Unable to get the PivotFields property of the PivotTable class." and the error points out the line: Set Field = pt.PivotFields("Category").

Would you please help me to understand what would possibly cause this error and the methods to correct it?

All the best,



Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'This line stops the worksheet updating on every change, it only updates when cell
'H6 or H7 is touched
If Intersect(Target, Range("H6:H7")) Is Nothing Then Exit Sub

'Set the Variables to be used
Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String

'Here you amend to suit your data
Set pt = Worksheets("Sheet1").PivotTables("PivotTable1")
Set Field = pt.PivotFields("Category")
NewCat = Worksheets("Sheet1").Range("H6").Value

'This updates and refreshes the PIVOT table
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With

End Sub

 

[theThirdMan]

Dear jorismoerings,

Your quick reply is much appreciated. I kindly ask you to amend the code to ensure that if pivot item does not exist then pivot table do not show any data (possibly filtered as "(blank)").

All the Best,

M

For anyone else that was having trouble with this. Not proficient in VBA, but was able to do this by adding these lines to the bottom (underneath the first With pt):

With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With

With pt
If Field.CurrentPage = "(All)" Then
Field.CurrentPage = "(blank)"
End If
pt.RefreshTable
End With

 

[WyattEarp20]

I have tried to tailor this to my own spreadsheet and pivot table but it keeps debugging at the Field.ClearAllFilters line. Can someone please take a quick look at my code below and try to help me fix this?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'This line stops the worksheet updating on every change, it only updates when cell
'H6 or H7 is touched
If Intersect(Target, Worksheets("CY PT").Range("E2")) Is Nothing Then Exit Sub


'Set the Variables to be used
Dim PT As PivotTable
Dim Field As PivotField
Dim Year As String


'Here you amend to suit your data
Set PT = Worksheets("CY PT").PivotTables("CY PT")
Set Field = PT.PivotFields("Fiscal Year")
Year = Worksheets("CY PT").Range("E2").Value


'This updates and refreshes the PIVOT table
With PT
Field.ClearAllFilters
Field.CurrentPage = Year
PT.RefreshTable
End With




End Sub

 

[horrellbt01]

Try This

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'This line stops the worksheet updating on every change, it only updates when cell
'H6 or H7 is touched
If Intersect(Target, Worksheets("CY PT").Range("E2")) Is Nothing Then Exit Sub


'Set the Variables to be used
Dim PT As PivotTable
Dim Field As PivotField
Dim NewCat As String


'Here you amend to suit your data
Set PT = Worksheets("CY PT").PivotTables("CY PT")
Set Field = PT.PivotFields("Fiscal Year")
NewCat = Worksheets("CY PT").Range("E2").Value


'This updates and refreshes the PIVOT table
With PT
Field.ClearAllFilters
Field.CurrentPage = NewCat
PT.RefreshTable
End With




End Sub

 

[WyattEarp20]

Your only change was changing Year to NewCat correct? That didn't fix the problem. I appreciate your response though.

 

[WyattEarp20]

It seems like it has something to do with this specific Pivot Table for some reason, I changed all the names on the example file I found to match mine and it worked fine. It just won't clear the filters for this specific Pivot Table

 

[WyattEarp20]

Ok, so I discovered the problem but the fix doesn't really work for me. The problem was that I had a Fiscal Period field in my Columns which the example spreadsheet doesn't have anything in the columns. I need the Fiscal Period in the columns, can someone help me with how to alter the below code to still work with Fiscal Period in my columns? It debugs on Field.ClearAllFilters and Field.CurrentPage = Year lines.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)If Intersect(Target, Worksheets("CY PT").Range("E2")) Is Nothing Then Exit Sub


Dim PT As PivotTable
Dim Field As PivotField
Dim Year As String


Set PT = Worksheets("CY PT").PivotTables("CY PT")
Set Field = PT.PivotFields("Fiscal Year")
Year = Worksheets("CY PT").Range("E2").Value


With PT
Field.ClearAllFilters
Field.CurrentPage = Year
PT.RefreshTable
End With


End Sub

 

[Zenfold]

Thanks Horrellbt01 the below code is excellent .

This is what I ended up going with - works well for me. This updates all pivot tables with the "Color" field with what is found at the target address of c2.
Dim WS As Worksheet
Dim pt As PivotTable
Const strField As String = "Color"

If Target.Address = "$C$2" Then


For Each WS In ThisWorkbook.Worksheets
For Each pt In WS.PivotTables
With pt.PageFields(strField)
.ClearAllFilters
.CurrentPage = Target.Value
End With
Next pt
Next WS
End If

I have found a slight problem using it with my report.
i have been using it to alter the date on a range of pivot tables to show sales from depots.
if one depot has not had a sale yet in Month X the filter will be set to all data. How could i get it to select the blank filter option instead?

Thank you very much for your time.
Zen.

 

[neeravamit]

Hi-Thanks, It has helped me this far-However in one of my xls sheet I need multiple filters linked to multiple cells for selction of values (Which can in turn be from a list of data validation).
How can I use the same code by altering it:
I am a novice in code and may share my sheet with you if that's needed

 

[neeravamit]

Dear jorismoerings,

Your code works. However I need to do this in Pivot for multiple filter that refer value from Multiple cells (Lets say playing with three filters of pivot)
what alteration should be made in the code above? I am a novice so any detail would be awesome

Would you need to see the xls sheet I am referring?

Regards
Amit S

​

 

[IrishChristof]

This is very helpful, but is there a way to do it with several selections?