Filter specific date in a Pivot table

[Pranesh]

Hello All,

Im trying to filter a specific date in a pivot table but im receiving "Application-deifned or object-defined error".

I tried to record macro and got the below output, but while i try to run the same recorded output again im getting the above mentioned error.

ActiveSheet.PivotTables("PivotTable4").PivotFields("Date"). _
        ClearAllFilters
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Date"). _
        CurrentPage = "9/18/2020"

In sheet1 range("A5") i have a date and that should be applied in the pivot table. Can someone help me..

 

[PankajMaheshwari]

Hi @Pranesh try this:

Sub change_filter()
Dim curdate As String

curdate = ActiveSheet.Range("A5").Value

Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("Date").CurrentPage = curdate

End Sub

Note that ".CurrentPage" is used to refer filter section of Pivot table.

 

[Pranesh]

Sub change_filter() Dim curdate As String curdate = ActiveSheet.Range("A5").Value Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("Date").CurrentPage = curdate End Sub

Hi Pankaj - Thanks for responding. I tried your code and now im getting "Subscript out of range error" in the below code

Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("Date").CurrentPage = curdate

 

[PankajMaheshwari]

@Pranesh you need to make sure few things.
1. If your Sheet name, Pivot table name, and filter name are different from what is there in code, you need to make those changes on your own.
2. Your date format in cell A5 and in pivot filter should be same. Example, I was using 31-Mar-20.

If it still doesn't solve the problem, you can share your data and that way, I can help you better.

 

[Pranesh]

@Pranesh you need to make sure few things.
1. If your Sheet name, Pivot table name, and filter name are different from what is there in code, you need to make those changes on your own.
2. Your date format in cell A5 and in pivot filter should be same. Example, I was using 31-Mar-20.

If it still doesn't solve the problem, you can share your data and that way, I can help you better.

Yes Pankaj.. I have changed sheet name, pivot table name & pivot field name in my file and then only i tried this code.
The date format in cell A5 & Pivot are mm/dd/yyyy.

 

[PankajMaheshwari]

Hi @Pranesh , here's the data I used to test the code. Once I change the date in cell A5 and run the code (by pressing Alt+F8 and selecting change_filter), it's working.