Filter specific date in a Pivot table

Pranesh

Board Regular
Joined
Jun 29, 2014
Messages
219
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.

VBA Code:
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..
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

PankajMaheshwari

New Member
Joined
Sep 7, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
Hi @Pranesh try this:

VBA Code:
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

Board Regular
Joined
Jun 29, 2014
Messages
219
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

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

PankajMaheshwari

New Member
Joined
Sep 7, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
@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

Board Regular
Joined
Jun 29, 2014
Messages
219
@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

New Member
Joined
Sep 7, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
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.
Capture.PNG
 

Watch MrExcel Video

Forum statistics

Threads
1,118,771
Messages
5,574,143
Members
412,574
Latest member
shadowfighter666
Top