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

 

[PeakyBlinder]

Hi

Are you able to use a lookup cell that is on a different worksheet? I have tried this and keep getting a RunTime error '1004'

The code I am using is:

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, Sheets("OTD Summary").Range("L3:L4")) 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 = Sheets("Pivot Data").PivotTables("PivotTable1")
Set Field = pt.PivotFields("CustomerID")
NewCat = Worksheets(3).Range("L4").Value

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


End Sub