Pivot Table Criteria Missing Data

[farmsie]

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.

 

[MSweetG222]

Chris,

Try clearing and refreshing the pivot table cache.

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

Hope that works for you.

MSweetG222

 

[farmsie]

Chris,

Try clearing and refreshing the pivot table cache.

Worksheets("Sheet4").PivotTables("PivotTable1").PivotCache.MissingItemsLimi­t
= xlMissingItemsNone
Worksheets("Sheet4").PivotTables("PivotTable1").PivotCache.Refresh
Worksheets("Sheet4").PivotTables("PivotTable1").PivotCache.MissingItemsLimi­t
= xlMissingItemsDefault
Worksheets("Sheet4").PivotTables("PivotTable1").PivotCache.Refresh

Hope that works for you.

MSweetG222

--
Thx
MSweetG222










- Show quoted text -

Thanks MSweetG222, but no luck I'm afraid. This is similar to code
I'm already using, although I hadnt considered the second cache
refresh. Still, your code works and does the refresh but still, when
I click on the drop down the data is incorrect, yet if I move that
criteria drop down out of the pivot criteria area, refresh the pivot
table, and put it back in everything is ok. Its driving me mad!!!

I recorded the macro to do that automatically. It produced the
following code..

ActiveSheet.PivotTables("EnqOrderPeriod").PivotFields("Exec
Location").Orientation = xlHidden
ActiveSheet.PivotTables("EnqOrderPeriod").PivotCache.Refresh
With ActiveSheet.PivotTables("EnqOrderPeriod").PivotFields("Exec
Location")
.Orientation = xlPageField
.Position = 3
End With

This, when run, fixes the problem .....but...

I'd like to loop around all the 13 or so pivot tables in the workbook
and run some code against them to make sure this problem is resolved
automatically (as the end users are not going to be able to understand
if there is a problem) and unless I manually specify every single
pivot field within the Page Field (criteria) it is not going to work.

Thanks for your help, its much appreciated.

Chris.

 

[Roger Govier]

Hi

This is a known problem in Excel versions below XL2007.
Debra Dalgleish has code to clear the old items on her site
http://www.contextures.com/xlPivot04.html

 

[MSweetG222]

Chris,

Roger's right. Go to Contextures website and review the code at
http://www.contextures.com/xlPivot04.html#Clear2002

Instead of using her code inside the loop, replace it with your code.
In her code "pt" would equal "ActiveSheet.PivotTables("EnqOrderPeriod") in
your code". That will get you your loop.

I don't understand why the
Worksheets("Sheet4").PivotTables("PivotTable1").PivotCache.MissingItemsLimi­t= xlMissingItemsNone line of code didn't work. Hmmmm.