Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

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

Tam
0
Tamalaine
Asked:
Tamalaine
1 Solution
 
marius8mCommented:
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?)
0
 
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
0
 
bruintjeCommented:
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()
    ActiveSheet.PivotTables("PivotTable1").RefreshTable
End Sub



HAGD:O)Bruintje
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
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
0
 
bruintjeCommented:
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

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

Tam
0
 
bruintjeCommented:
thanks Tam until the next time
0
 
kjdubyaCommented:
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!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now