Pivot Table Autofilters Not Clearing On Refresh

**kkatzer** · 09-13-2007, 12:29 PM

Hello,

I have an application which presents an interface to users so they can select parameters for data retrieval. When they are satisfied with the parameters they have entered they then click a button which will cause data to be retrieved and displayed in an Excel app. Just for reference this is a production reconciliation report.

The flow is:
1) User requests report by entering in parms, clicking button.

2) Data is retrieved

3) Excel reconciliationMaster.xls file is copied to recon_<yyyy-dd-mm hh:mm:ss>.xls. (the master file template for all reports)

---- the copied file is used from this point on ----

4) The program creates an Excel app

5) The sheet which holds source data for pivot tables is cleared and repopulated with the newly retrieved data and the data range is redefined.

6) The program walks through each pivot table in the file and performs a refresh.

The problem is:
The autofilters behave oddly. If I take an item and move it into the Page Field area, the data in the autofilter looks fine. If I take the same item and move it into the Column Fields area, the autofilter contains entries for the current data as well as any data which was contained in the master file. The data itself looks fine, it just looks like the autofilters are not being cleared for the Column Fields when a refresh is performed.

I have googled and wandered through the object models (which I will continue to do), but I cannot see how to programmatically force a clear of the autofilter information for the Column Fields for a set of pivot tables.

Any ideas?

Thanks...

Kim

**Ron Coderre** · 09-13-2007, 01:00 PM

If I understand you correctly, the Pivot Table dropdowns contain obsolete items from previous refreshes.

You have a couple options:
1) Some time ago, I wrote PivotPlay, a free add-in that works on Pivot Tables. One of its features is that it clears obsolete items with the click of a button.
It's available here:
http://www.contextures.com/xlPivotPlay01.html

2) See Debra Dalgleish's coverage of your issue and a few options on handling it.
http://www.contextures.com/xlPivot04.html

Does either of those help?