Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Getting "Unable to set CurrentPage property of PivotField class" error

Tags:

excel

vba

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
like image 509
Robin A Avatar asked Jan 15 '16 18:01

Robin A


1 Answers

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

like image 188
momo Avatar answered Oct 19 '22 14:10

momo