Automatically Refresh Pivot Table with new date

Hello Guys,

I have a pivot table based of a massive source data that I update manually. Also, whenever I add a new date to the source data, I would like this new date to be added “by default” to my pivot table when refreshed. I Think I need a macro for that, but I’m trying to find a simple way to do it.

Basically, I have 10 pivot tables set up based of my source data, and I don’t want to go to each and every filter I set up for those tables and tick the new date in the filter so it can be taken into account.

Please if I’m not that clear, feel free to ask me more details.

Thank you guys

I'm on Excel 07

It doesn't work. Also, I never heard about F9 as a shortcut to refresh pivot table. I know ctlr +alt + F5 (refresh all) or F5 to refresh a specific table....

I tried your solution by adding dummy data in data source and see what would happen if I hit F5....but didn't work

Thanks though

Ok, excel328, this was my old way of refreshing all pivot tables. But here's the problem, I have filters set up for every pivot table, so when I add the data in source data.......when I refresh, the new data doesn't get picked up in the pivot table. Instead, I have to go and check "Jun-10" in the filter in order to make the new data appear in the pivot table.

So....Any clues on how to make my life easier

Thanks again

First off, thanks for your file (PS: I know this sumproduct formula with criteria, but how can it return “TRUE”, “FALSE”, especially if you sumproduct words? could you explain that to me later

With respect to my problem, you will see that I added a dummy data highlighted in red in the tab “Exposures”, but when I refresh All ctlr+alt F5 (not using the code, and I didn’t try the code yet)…..the new data doesn’t get picked up in the “All Funds” tab. I have to go manually at the Date Filter at the range “AF115” and tick the newly added date.

Also, How do you run your macro? I inserted a module and copied your code, but nothing!
Thanks

Wonderful excel328 you made my day man. By Jul-50 I meant 07/31/2050, I rectified this on my source data because it considered as 1950 (it's my mistake ). Also, I applied a filter "is After or Equal" to Jan-07 (the date I usually test my data for) and When I refreshed.....Miracle....It is picking up the new date.....Thanks man and sorry for all the headaches.