Filtering Pivot Table

[Niraj Baraili]

Hi Experts,

I am trying to apply filter pivottable based on a cell value as below but my code is not working and it's throwing up error 1004.

Any input on this is appreciated ?

Worksheets(1).Activate
    Dim pt As PivotTable
    Dim Field As PivotField
    Dim rValTL As String
   
    Set pt = Worksheets(1).PivotTables("pivottable4")
    Set Field = pt.PivotFields("Date")
    rValTL = Wb2.Sheets("Macro").Range("K1").Value
    With pt
        Field.ClearAllFilters
        Field.CurrentPage = rValTL
        pt.RefreshTable
      End With

 

[Luke M]

Which line of code is causing the error? When you hit debug, which line it highlighted?

 

[Niraj Baraili]

Hi Luke,

My code stops at Field.CurrentPage = rValTL and throws 1004 'application defined or object defined error.

 

[Chihiro]

Try

Debug.Print rValTL

And see what comes up in immediate window.
Likely cause of the error is date format mismatch (or simply that rValTL isn't found in "Date" Field).

 

[Niraj Baraili]

rValTL=4/4/2016

 

[Chihiro]

So do you have exact match in your "Date" field?

 

[Niraj Baraili]

Yes. But don't know why it's not working.

 

[Niraj Baraili]

Hi Chihiro,

The issue was due to date format only. It's working fine now.

Thank you very much for bringing it to notice.