Debra Dalgleish's DeleteMissingItems2002All()

[anthos.nicolaides]

I was desperate to find a code for removing all new items that appear
after a pivot refresh and while I was excited having found such a code,
I've got the following problem.

I paste the code in the editor but when I try to run the macro it gives
me a run time error '1004' and when I go to the debug screen I get the
line "pt.PivotCache.MissingItemsLimit = xlMissingItemsNone"
highlighted.

I'm using Excel 2003 so the version is the correct one. I tried
running the earlier version ( DeleteOldItemsWB() )and it did clear the
old items but what I want is the opposite which is to prevent the new
items from appearing and the older version only clears the old items.

Any hints boys & girls??

Thanks.

 

[Debra Dalgleish]

The code only works for non-OLAP pivot tables. Could that be the problem?

 

[anthos.nicolaides]

Thanks Debar for the reply...

My Pivots are OLAP so that's the problem...is there a way around it??

I'm pretty desperate because I run lots of pivot tables at work
(through OLAP) and whenever a new items is added to the source data, it
appears on my pivot right after I refresh. I may have 50 different
pivot tables for different reports and I have to go back and recheck
the items one by one each time. The best I could do is go in the table
options and check the option for manual sorting. In this way, all new
items are added below the existing ones. But since I cannot delete any
rows in a pivot, I have to copy/paste as values and delete etc etc...

Thanks again Debra...

 

[Debra Dalgleish]

I don't know of a workaround for OLAP pivot tables. Can you run the
other code, to clear the old items, after you get new data?

 

[anicolaides]

Yes Debra, the old items did clear using the other code.

Thanks.

Anthos