Pivotitems collection has items that can't be displayed?

Reid

Well-known Member
Joined
Oct 29, 2004
Messages
593
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.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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

Well-known Member
Joined
Oct 29, 2004
Messages
593
Tony,

That worked perfectly! Thanks for your help.

Reid
 

Forum statistics

Threads
1,141,599
Messages
5,707,311
Members
421,502
Latest member
PULBAG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top