Filter Pivot Table with Input Box

Question

Need some help, just can't see what is wrong here. Trying to Filter a Pivot Table by date entered in Input Box. I keep getting error;

1004 Unable to set the PivotFields property of the PivotTable class.

However, my field is called "Financial Month" so have no idea why it isn't working.

My code is:

Sub Select_Pivot()

    Dim DateSelect As String

    DateSelect = InputBox(Prompt:="Search Date", Title:="Enter Date as <Month Year> i.e. May 2017")
    Sheets("CPR").PivotTables("PivotTable1").PivotFields("Financial Month") = DateSelect

End Sub

Any ideas? Been trawling the internet and just can't find a solution. Greatly appreciate any advice here please.

If you go to the Immediate Window (Ctrl+G) and type ?Sheets("CPR").PivotTables("PivotTable1").PivotFields("Financial Month").Name +Enter, what do you get? — Excelosaurus
I get "Reference not Valid" but when I look at the Pivot Table fields, it clearly shows as "Financial Month" - thank you for replying so quickly @Excelosaurus — Jayson D
If your PivotField is a Filter for your whole PivotTable, the property you should be assigning to is .CurrentPage as in (...).CurrentPage = DateSelect; the default property for a PivotField is its Name. If this doesn't work, please go to the Immediate Window and try ?Sheets("CPR").Name, then ?Sheets("CPR").PivotTables("PivotTable1").Name — Excelosaurus
Thanks @Excelosaurus - I did try '.CurrentPage' but I get the same 1004 error — Jayson D
I did a GoTo for both as you listed above and get "Reference not Valid" which again is weird as even the sheet is clearly labelled "CPR" — Jayson D

Shai Rado Shai Rado · Accepted Answer · 2017-11-02T06:00:36

Try the code below, explanations inside the code's comments:

Option Explicit

Sub Select_Pivot()

Dim DateSelect As String
Dim PvtTbl As PivotTable
Dim PvtItm As PivotItem

DateSelect = InputBox(Prompt:="Search Date", Title:="Enter Date as <Month Year> i.e. May 2017")

' set the Pivot-Table object
Set PvtTbl = Sheets("CPR").PivotTables("PivotTable1")

With PvtTbl
    With .PivotFields("Financial Month")
        .ClearAllFilters
        For Each PvtItm In .PivotItems ' loop through all items in "Financial Month"
            If PvtItm.Name = DateSelect Then
                PvtItm.Visible = True
            Else
                PvtItm.Visible = False
            End If
        Next PvtItm
    End With
End With

End Sub

Filter Pivot Table with Input Box

1 Answers