Debra Dalgleish's DeleteMissingItems2002All()

  • Thread starter Thread starter anthos.nicolaides
  • Start date Start date
A

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.
 
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...
 
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?
 
Back
Top