We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

PivotTable values not clearing after refreshing data

Ed_CLP
Ed_CLP asked
on
Medium Priority
325 Views
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
  pc.Refresh
Next pc

Exit Sub
Comment
Watch Question

Most Valuable Expert 2012
Top Expert 2012

Commented:
What version of Excel are you running?
Most Valuable Expert 2012
Top Expert 2012
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

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?
Most Valuable Expert 2012
Top Expert 2012

Commented:
Did you sort it out?

Dave
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.