PivotTable values not clearing after refreshing data

Posted on 2011-04-28
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
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
    LVL 41

    Expert Comment

    What version of Excel are you running?
    LVL 41

    Accepted Solution


    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

    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 41

    Expert Comment

    Did you sort it out?


    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
    Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    731 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now