Tamalaine
asked on
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
Thanks in advance
Tam
ASKER
Yep, using office / Excel 97! Changing to office 2000 isn't really possible unless that's the only way to deal with the issue
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("P
End Sub
HAGD:O)Bruintje
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("P
End Sub
HAGD:O)Bruintje
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your info re: using experts exchange but it's alright, have the points anyway!
Regards
Tam
Regards
Tam
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!
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!
Are you sure having the automatic calculation activated? (If you push F9 something change?)