I'm trying to use VBA to automatically update the pivot table filter based off the user's selection from a cell entry. I believe everything is working correctly up to line 11 which says "Field.CurrentPage = NewCat". This is where the issue lies. The code clears my filter as instructed in the previous line but when it gets to the code instructing it to select the new data I get the error that reads "Run-time error 1004. Unable to set CurrentPage property of PivotField class".
Below is the what I have so far and I'm just looking how to revise row 11 so it selects the new input to use in the pivot table filter. I appreciate any help I can get on this. I'm very new to vba & have struggled with this for far too long!
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("C3:C4")) Is Nothing Then Exit Sub
Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String
Set pt = Worksheets("Pivot").PivotTables("PivotTable1")
Set Field = pt.PivotFields("[Range].[Site].[Site]")
NewCat = Worksheets("Interface").Range("C3").Value
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With
End Sub
Hoping this will be beneficial to someone who is experiencing the same "1004 error" when attempting to set the "CurrentPage" property of the PivotField class.
For me, it finally worked when I replaced the "CurrentPage" property with "CurrentPageName" as such:
With pf
.ClearAllFilters
MsgBox "The PivotField is: " & pf.Value
MsgBox "The current PivotField value is: " & .CurrentPageName
.CurrentPageName = NewCat
End With
My pivot table have what was called "page level filters": enter image description here
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With