Flush Pivot Table Cache

[Danny]

Hi People

I have looked around for ages now and all solutions don't seem to refresh one of my pivot tables.

One filter on the pivot table makes use of dates, pulling the dates from source data on another worksheet. However the worksheet has changed and older dates are no longer in the source, however the pivot still sees them.

I have tried changing "number of items to retain per field: none"
Removing the filed, refreshing the table, adding it back again
Restarting Excel
Creating a new table with the same fields

If possible some VBA to do this would be really useful, I need to ensure that old dates do not remain as this is part of a larger macro/ script.

Any ideas would be greatly appreciated

Thanks you in advance
Danny

 

[Hui]

Make sure your Pivot table doesn't have a Filter applied that is locking the Dates?

The best place to find info on Pivot table caches is http://www.contextures.com/xlPivot04.html

 

[Danny]

Hi Hui

Thank you for your post, I have however already ran through both options on that website.

I have deleted the source page, the pivot table and restarted Excel. Re-added the source data and created a new pivot. It has removed the cached dates, just means I have to recreate the pivot. I didn't create it in the first place and there has been some odd handy work on it.

Problem side stepped rather than resolved at present.

Thanks again
Danny

 

[SirJB7]

Hi, Danny!
Consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.
Regards!