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?

Thanks in advance

Who is Participating?
bruintjeConnect With a Mentor Commented:
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. and we can always post after accepting an answer

Are you using office97? Where was a bug on this things.
Are you sure having the automatic calculation activated? (If you push F9 something change?)
TamalaineAuthor Commented:
Yep, using office / Excel 97!  Changing to office 2000 isn't really possible unless that's the only way to deal with the issue
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Hello Tamalaine

you could try it with VBA

-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.

Private Sub Worksheet_Calculate()
End Sub

TamalaineAuthor Commented:
I posted the question so long ago that I've lost the dataset I would have tested it on so I can't test it!

You're welcome the points anyway but would using VBA to refresh the data in the pivot table be any different from doing it manually (which didn't work)?

(NB I'll give you the points after you respond because I can't remember if you can post comments after accepting a comment as an answer)

From what I've learnt since posting the question, I suspect a) the pivot table object may have been corrupted and b) the solution probably involved purging the pivot cache object.

Regards Tam
TamalaineAuthor Commented:
Thanks for your info re: using experts exchange but it's alright, have the points anyway!

thanks Tam until the next time
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!
All Courses

From novice to tech pro — start learning today.