VBA "Error 1004: unable to get the PivotFields property of the PivotTable class "

Question

I made a PQ append query, send reults to Data Model and then uploaded the pivot table to the excel sheet. Now I'm trying to apply filter based on the cell value.

Here's my code:

Sheets("RE_2").Select
Dim SK
Dim re_outcome
Dim re_income


SK = Range("I2").Value * 1
re_outcome = Range("H4").Value
re_income = Range("H5").Value
ActiveSheet.PivotTables("outcome_re_1").PivotFields("[RE_total_from_IR6].[entity].[entity]").ClearAllFilters
ActiveSheet.PivotTables("outcome_re_1").PivotFields("[RE_total_from_IR6].[entity].[entity]").CurrentPageName = SK

The last row produces an Error 1004: unable to get the PivotFields property of the PivotTable class .

I tried to change CurrentPageName to CurrentPage but got the same error. I appreciate any help on the described error.

P.s. I used debugger to find PivotFields with this code:

Dim pt As PivotTable
Dim pf As PivotField

Set pt = Worksheets("RE_2").PivotTables("outcome_re_1")

For Each pf In pt.PivotFields
    Debug.Print pf.Name
Next

The output was as follows:

• [RE_total_from_IR6].[entity].[entity]
• [RE_total_from_IR6].[type].[type]
• [RE_total_from_IR6].[bank].[bank]

Answer 1

The error is because no PivotField exists with the name "[RE_total_from_IR6].[entity].[entity]". The PivotFields name must match the name in the header of the pivot table. It should be the same as the PivotField.Caption property. Try using the debugger to look for the correct name of the PivotField.

Answer 2

I managed to make the code work by changing the last line of code that triggered the 1004 error.

I changed ActiveSheet.PivotTables("outcome_re_1").PivotFields("[RE_total_from_IR6].[entity].[entity]").CurrentPageName = SK to ActiveSheet.PivotTables("outcome_re_1").PivotFields("[RE_total_from_IR6].[entity].[entity]").CurrentPageName = "[RE_total_from_IR6].[entity].&[" & SK & "]"