View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
MSweetG222 MSweetG222 is offline
external usenet poster
 
Posts: 158
Default Pivot Table Criteria Missing Data

Chris,

Try clearing and refreshing the pivot table cache.

Worksheets("Sheet4").PivotTables("PivotTable1").Pi votCache.MissingItemsLimit
= xlMissingItemsNone
Worksheets("Sheet4").PivotTables("PivotTable1").Pi votCache.Refresh
Worksheets("Sheet4").PivotTables("PivotTable1").Pi votCache.MissingItemsLimit
= xlMissingItemsDefault
Worksheets("Sheet4").PivotTables("PivotTable1").Pi votCache.Refresh

Hope that works for you.

MSweetG222




--
Thx
MSweetG222



" wrote:

Hi,

I wonder if anyone has had similar problems.... I have created an
automated Management Reporting Dashboard. Data is being imported from
external sources, which are pivoted using static pivot tables and
dynamic named ranges.

In general it all works fine, except that from time to time the pivot
criteria drop downs dont show the correct data to choose from. The
code, when importing the data, cycles round all pivot tables and
refreshes. Also when you clear the spreadsheet of old data the code
turns of the saving of data within the Pivot table so its all clear.

No matter what I do, the only way for the pivot to refresh itself
properley and show the correct data within the drop downs is to delete
and recreate the pivot.

Any thoughts would be much appreciated.

Chris.