Pivotitems collection has items that can't be displayed?

[Reid]

Here is a new one for me. I have a pivotfield that has 300 or so pivotitems. This pivotfield is a page field in my pivottable. I have some code that loops through all of the pivotitems to display each as the current page. Pivotitems(157) causes the following error:

Unable to set the _Default property of the PivotItem class

If I loop through the collection, I can write out all of the pivotitems, including pivotitems(157) without difficulty. However, when I go to the drop down box of the page field to manually choose the value of pivotitems(157), it is not listed. Clearly this is why my code is hanging up at this point. Still, how can the pivotitems collection have an item that then can not be displyed in the pivotfield's drop down box?

Thanks for any help.

 

[acw]

Hi

I've come across this problem. Seems that the pivot cache retains details from a previous load of data.

The only way I've been able to overcome it in code is to
1) make the pivot field involved hidden
pivottable("name").pivotfields("field").orientation = xlhidden

2) Refresh the cache
PivotTables("name").PivotCache.Refresh

3) reinstate the pivot field
PivotTables("name").PivotFields("field").Orientation = xlPageField

HTH

Tony

 

[Reid]

Tony,

That worked perfectly! Thanks for your help.

Reid