PivotTable values not clearing after refreshing data

Posted on 2011-04-28
Medium Priority
Last Modified: 2012-08-13
I have a multi-sheet workbook that imports SQL data and formats it with PivotTables and Charts.
The imported data does not always contain the same categorized information.  I'm having a problem where if data is imported but does not contain the same categories as the previous import, the PivotTable filters retain the previous set of values.  Example - I import data and the filter field contains values A,B,C,D,E.  I clear worksheets and import new data.  This time the filter field contains values A,B,C (no D,E).  The PivotTable filter fields either retain the D  and E values or default to 'All'.  I'm using the code below to try to clear all PivotTables and caches, but it doesn't appear to be working;

Sub ClearPivotCache()
'prevents unused items in non-OLAP PivotTables
'pivot table tutorial by contextures.com
Dim pt As PivotTable
Dim ws As Worksheet
Dim pc As PivotCache
On Error GoTo errMyErrorHandler

'change the settings
For Each ws In ActiveWorkbook.Worksheets
  For Each pt In ws.PivotTables
    pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
  Next pt
Next ws

'refresh all the pivot caches
For Each pc In ActiveWorkbook.PivotCaches
  On Error Resume Next
Next pc

Exit Sub
Question by:Ed_CLP
  • 3
LVL 42

Expert Comment

ID: 35488070
What version of Excel are you running?
LVL 42

Accepted Solution

dlmille earned 1000 total points
ID: 35488079
See: http://www.contextures.com/xlpivot04.html

Your code seems similar to suggested.  However, in Excel 2007+, there's a pivot table option to set, re: retaining items - if you clear that, there appears to be no need for VBA code (I've not tested).


Author Comment

ID: 35488212
I'm using 2007.  Where is the option for retaining items?  That would explain it.
 The alternative would be to manually set the cell value with VBA.   Can you do that with a PivotTable?
LVL 42

Expert Comment

ID: 35488245
Did you sort it out?


Featured Post

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question