Excel 2000 Pivot Table Page Field items accumulating

[Steve Crago]

Hi,

To anyone that can help sort out a new Excel 2000 'feature'.

I have found that Excel 2000 pivot table page fields are accumulating items.
When looking at the "Hide items" list in the Pivot Table field settings I
can see items listed that have been removed from the database that the pivot
table is using.

I need to be able to remove these items from the page fields because when
the field is used as a row or column these items are displayed in the drop
down list as selectable. This appears to be a new 'feature' of Excel 2000
that is causing us considerable grief. The manual Hide option is not
suitable because the data is very dynamic.

Is anyone able to offer some advice on this?

[Tom Ogilvy]

If I correctly understand the situation I don't believe this is new
behavior. The pivot table was doing this back as far as Excel 97 for sure.

Here is what MS says about it:

http://support.microsoft.com/support/kb/articles/Q202/2/32.ASP
XL97: Incorrect Field Names Appear in PivotTable Field Dialog Box

Additional information:
The solution requires VBA. The following was posted by Stephen Bullen:

=====================================
http://www.deja.com/[ST_rn=ps]/getdoc.xp?AN=581662312&fmt=text

From: Stephen Bullen <Ste...@BMSLtd.co.uk>
Subject: Re: Pivot Tables: Persisting field values
Date: 04 Feb 2000 00:00:00 GMT
Message-ID: <VA.0000074...@bmsltd.co.uk>
Content-Transfer-Encoding: 8bit
References: <87ck88$ckg$1...@hercules.iupui.edu>
Content-Type: text/plain; charset=iso-8859-1
Organization: Business Modelling Solutions Ltd
Mime-Version: 1.0
Reply-To: Ste...@BMSLtd.co.uk
Newsgroups: microsoft.public.excel.misc

Hi Victor,

> Is there any way to
> get rid of the no longer existing values, short of rebuilding the table
from
> scratch?

Sure, the following code will remove all such labels from a pivot table. In
your case, though, it sounds as though you want to selectively remove them -
i.e. remove them from the "Departments" field, but not from some of the
other
category fields.

Sub RemoveOldLabels()

Dim oPiv As PivotTable
Dim oField As PivotField
Dim oItem As PivotItem

Set oPiv = ActiveSheet.PivotTables(1)

For Each oField In oPiv.PivotFields
If oField.Name <> "Data" Then
For Each oItem In oField.PivotItems
If oItem.RecordCount = 0 And Not oItem.IsCalculated Then
oItem.Delete
Next
End If
Next

End Sub

Note that this retains calculated items (which by definition don't have any
source records).

Regards

Stephen Bullen and Jennifer Campion
Microsoft MVPs - Excel
http://www.BMSLtd.co.uk (http://208.49.24.208)

================>

Regards,
Tom Ogilvy

"Steve Crago" <cr...@vital.com.au> wrote in message
news:pz7O5.17721$SF5.4...@ozemail.com.au...