CODEWITHSUNDEEP
vbaexcelpivotinputbox
Jayson D
Jayson D

Reputation: 27

Filter Pivot Table with Input Box

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.

Upvotes: 0

Views: 1411

Answers (1)

Shai Rado
Shai Rado

Reputation: 33672

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

Upvotes: 1

Related Questions