Excel: How do I remove redundant selections from a pivot table's page fields?

I have a pivot table which is regularly refreshed from a large DBF datasource (which is accessed by opening it within EXCEL). However when the pivot table is refreshed, some of the pivot table's page fields continue to show selections relating to the previous dataset. When these are selected a blank pivot table is generated because such items are not represented in the new dataset. Why are these out of date selections shown and more importantly how can they be removed? Is VBA required?

Hello Tam, the bit of VBA would do the same as refreshing by hand, but if it would work or not in your situation we'll never know :)
btw we can also aska mod ot move this to PAQ, and refund your points.

-Hit ALT-F11 to bring up the VBA editor.
-Double-click on the appropriate worksheet on the tree in the upper-lefthand area of the screen, the area that shows all the sheets and any modules, etc.
-Paste in the following code. If you have named this pivot-table something other than "PivotTable1", then change the name in the .RefreshTable line.

Pivot tables can indeed become corrupt, particularly the pagefield cache. The fix is simple.

1) Make sure that there are no other pivot tables directly beneath pivot table that needs fixing (If there are, you will have to temporarily move the broken pivot table to a location underneath the last pivot table on the sheet. You can move it back to its original location once it's fixed).

2) Then, remove the page field from the table - right click anywhere in the table and select "Show Field List" from the popup menu, and then click and drag the page field back to the field list.

3) Next, right click the table again and select "Refresh Data".

4) Finally, go to the Field List and drag the original pagefield back to the pagefield postion on the pivot table. That should do it!

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…

The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…