pivot table dropdown lists have "old" values

[busyman5_au]

I have setup a pivot table infront of some data being brought in fro
other external XLS files. It works but not everything is refreshin
fully.

This seems to work OK with data rows being inserted/removed inline wit
what is happening in the source workbooks.

My problem is that the dropdown lists (eg above the ROWS area fields
continue to have values that no longer exist in the source worksheets
(eg I had a vlue once of "bbbb" in the source sheet but it is gone no
- but when I look at the dropdown list it is still there, even thoug
it doesn't exist in the imported data anymore.

What am I doing wrong in my refresh? or is tghere a magic refres
everything somewhere?

thanks

 

[Debra Dalgleish]

There are instructions here for clearing old items from a PivotTable
dropdown list:

http://www.contextures.com/xlPivot04.html

 

[busyman5_au]

Thanks Deborah,
THis seems to work (but only if I put it in the samesheet I a
working on..... Can I make it more generic to work in all sheets fro
one commandbutton (I have the button)
or even better automatically !!

And my boss wants to know... is this because of a bug/problem wit
Excel 2002 or is it a deficiency ion the way the product works?
IE will it maybe be fixed in a newer version if we upgrade ?

Thanks again

 

[Dave Peterson]

#1. Debra has two routines on that page--one for xl2k and before and one for
xl2002 (and later?).

The one for xl2k fixes all the pivottables on all the worksheets. The one for
xl2002 does one.

(It sounds like a bug in Deb's thinking! (oh, oh)).

If I steal a little from the xl2k version and plop it into the xl2002 version, I
can get this:

Option Explicit
Sub DeleteMissingItems2002B()
'prevents unused items in XL 2002 PivotTable
'If unused items already exist,
'run this macro then refresh the table
Dim pt As PivotTable
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
Next pt
Next ws

End Sub

You could make it kind of automatic by having the code run whenever you
activated a sheet.

Inside the ThisWorkbook module, paste this code:

Option Explicit
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.PivotTables.Count > 0 Then
Call DeleteMissingItems2002C
End If
End Sub

Then in a general module, you can have a (slightly different, again) version:

Option Explicit
Sub DeleteMissingItems2002C()
'prevents unused items in XL 2002 PivotTable
'If unused items already exist,
'run this macro then refresh the table
Dim pt As PivotTable
Dim ws As Worksheet

Set ws = ActiveSheet
For Each pt In ws.PivotTables
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
Next pt
End Sub

====
Just my opionion. I'd stick with the button and run it on demand. If you
didn't change anything, why ask for the update--especially each time you
activate a sheet??


#2. I'm still running xl2002, so I don't know if this has been addressed in
xl2003.