MobileMobile | Continue

Excel Bar

Excel Bar

Your excel questions will be responsed by our excel experts within 24hrs.Our service is free.

 Forgot Pass?
 Register Now
Find
Hot Search: Vlookup Match VBA
View: 187|Reply: 0

Pivot Table does not refresh with new data.

[Copy link]

34

Topics

1969

Posts

4182

Integral

Forum Legend

Rank: 8Rank: 8

Integral
4182
Post on 2-8-2017 13:08:04 | All posts |Read mode
I occasionally come across what I call a “Lazy Pivot Table:” that is, a pivot table that will not update with new data when refreshed.
I'm posting the issue here, because even though it shows up while running VBA code, manual manipulation of the pivot table shows the same problem.
For example, I have a report that tracks defects for wireless devices. The report is produced by VBA code. As part of the code, I read the list of models and set a pivot table filter to the model name. However, before I do that, I do a COUNTIF against the raw data to assure that the model exists in the data so the filter can be set.
On a rare occasion, maybe once out of every several hundred runs, the table “craps out.” I get an error message that the model name can’t be found and do I want to rename the current model name to the new name. Of course not.
The raw data is in an Excel Table and the pivot table uses this as its data source. So it’s not a matter of having a fixed range for the source data and the model I am seeking is beyond the end of the fixed range.
I refresh the pivot table and I still cannot find the model. I run the following code and I still can’t find the model.
Sub ClearPivot()
'prevents unused items in non-OLAP PivotTables
'pivot table tutorial by contextures.com
Dim pt As PivotTable
Dim ws As Worksheet
'change the settings
For Each ws In ActiveWorkbook.Worksheets
  For Each pt In ws.PivotTables
    pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
    pt.PivotCache.Refresh
  Next pt
Next ws
End SubI can build an exact duplicate of the pivot table and the duplicate pivot table works. This is my only work-around: clobber the table and rebuild it.
It is as if, even though the data source is still the Excel Table, the Pivot Table is stuck with old data. I’ve even re-confirmed the source, hoping that it would “toggle” the pivot table awake.
I can’t post the worksheet because (a) it’s very large, (b) it has a lot of proprietary data on it and (c) I can’t make the error happen if I build a sample workbook unless I am willing to run it several hundred times and even then there would not be a guarantee it will fail; not all of my workbooks do this.
So, has anyone seen this happen to them? Does anyone know why it is happening? Does anyone have a work-around better than removing and rebuilding the pivot table?
Reply

Props Report

Points policy of this forum

Archiver|Mobile|Small dark house|Contact us|Excel Bar

GMT-5, 9-24-2017 21:36 , Processed in 0.128206 second(s), 21 queries .

Powered by Discuz! X3

© 2001-2013 Comsenz Inc.

!fastreply! Top !return_list!