Changing The Dates On Pivot Table Filters Using VBA

Hi,

I have a Workbook with a lot of PivotTables on it.

I am trying to change the date on the filter to todays date..

Can someone guide me whats wrong with this code? I am unable to get it to work for some reason..

Please Login or Register  to view this content.

But, when I change it to this line, it suddenly works

Please Login or Register  to view this content.

I also have the Date in my underlying data returned by the Date function.

This is the code generated by the Macro Recorder, but that does not work when done using a variable or even a hard coded value for that matter

Please Login or Register  to view this content.

It's expecting the date as text (not as a serial date value) with the same date format as used on the sheet.
Try something like this. Change the date format to suit.

Please Login or Register  to view this content.

Hi,

Thank you for the reply..

I have tried that aswell, but does not seem to work. The dates on the file attached in Post #1 are in this format "d-mmm-yy"

Please Login or Register  to view this content.

Can you see the attachment once if possible?

Or should I be using the PivotField property?

My older version of Excel won't convert your version of pivot tables. So I cannot use your pivot table. I can make a new pivot table with your data and pivot on the dates with VBA. Note that your data does not have a date for 28-Aug-15. So you can not pivot on today's date.

Actually while trying I changed the dates in my Data to 28th Aug 15.

Can you attach the file created by you along with the working code?

Originally Posted by NeedForExcel

Actually while trying I changed the dates in my Data to 28th Aug 15.

Did you create a new pivot table after changing the date in the data? Changing the data and only refreshing does not add a new date to the pivot field dropdown.

Still returns the same error

Any help please? Someone?

Please try this approach to see if this works for you.....

Please Login or Register  to view this content.

It works, but it possesses pretty slowly.

I came up with this, which is working extremely fast, however, the date in Range A2, needs to be formatted exactly in the format thats on the base data.

Please Login or Register  to view this content.

But both the approach will be failed if you don't have the record for the mentioned date.

True that!

But fortunately, the Drop Down also has a query that runs to get the distinct dates. So it is not possible to have a date that's not there on the Pivot..

The approach is likely to fail, if there are more filters/No Filters/Different Filters on the Pivot Table, so I updated the code to this

Please Login or Register  to view this content.

Hi Need4XL,

A few years ago I was heavy into PivotItems and making multiple Pivot Tables have the same filters if ANY of them changed. See the attached which might have code to help you. If you change the filter of any of the 3 tables, the others will also change. See if this helps answer this question. Part of what I learned is making the Pivot Filter a multi filter (with the boxes to the left) creates a little different macro recorded code than what you have.

Hi,

It is 90% of what I was looking for.. Only my selection value came from a Drop Down List.

What I noticed is, In case of regular Text values, it is not as tuff, however when it get to Dates, it becomes a headache.

I also noticed, your date needs to be formatted in exactly the same way as on the Pivot Report Filter (Even though it is actual Date and Not Text)