way:Clearing a pivot table

**jzibton** · 07-10-2008, 01:54 PM

This question has been asked before without a solution given . . .

I have a fairly elaborate workbook that is building in size. (now about 70 MB) I have a sheet that is all data and multiple other sheets working off the data. I copy and paste new data (from a mdb) into the data sheet and then the other sheets update from this source. A couple of the analysis sheets are pivot tables.

Problem is, even if I clear out the data sheet completely and refresh the data in the pivot tables the drop down selection data is still showing up! The pivot table is blank (as it should be) but if you use the drop down arrow you see all the previous selections. I think that's why the file is growing in size!

Is there a way to 'really refresh' the pivot tables? This make sense?

**Andy Pope** · 07-10-2008, 02:41 PM

Does this help?
http://www.contextures.com/xlPivot04.html

**Andy Pope** · 07-11-2008, 03:47 AM

jzibton PM'd this follow-up question

You posted an answer on clearing a pivot table of old data. It works, kind of.
The info included some VB script to automatically delete old data but I've had very little experience with vb code. I tried to paste it into the 'Module1' area and into the general area under the pivot sheet and neither worked.

Do you mind going a step further and explaining where to paste this code?

**Andy Pope** · 07-11-2008, 03:52 AM

Assuming you have xl2002 or earlier.

The code needs to be placed in a standard code module, like Module1.
And then you need to run the macro DeleteMissingItems2002All, which will then process all sheets in that workbook.

way:Clearing a pivot table

LinkBack

Thread Tools

Rate This Thread

Display

way:Clearing a pivot table

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions