PivotTable values not clearing after refreshing data

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
Who is Participating?
dlmilleConnect With a Mentor Commented:
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).

What version of Excel are you running?
Ed_CLPAuthor Commented:
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?
Did you sort it out?

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.

All Courses

From novice to tech pro — start learning today.