Incorrect Field Names Appear in PivotTable Field Dialog Box

  • Thread starter Thread starter Mr.Ribit
  • Start date Start date
M

Mr.Ribit

I have found that items deleted from my source, standard worksheet,
are still appearing in the Pivot Table Drop down list. I have applied
the sollution in MS202232 but it did not clearup the outdated data. I
have refreashed the pivot table before and after runing the MACRO
"Delete_Fields"

Not sure what else can be done, I continue to search groups and MS.
********
This is the code for the MACRO based MS202232:

Sub Delete_Fields()
On Error Resume Next
For Each pvtfield In
Worksheets(PivotTables).PivotTables(PivotTable1).PivotFields
For Each pvtitem In pvtfield.PivotItems
pvtitem.Delete
Next
Next
ActiveSheet.PivotTables(PivotTable1).RefreshTable
End Sub
********
Did I miss something? Please don't assume previous knowledge on my
part, this is the first time I am using Pivot Tables.

Thank You
Thomas
 
To eliminate the old items from the dropdowns, in Excel 2002, you can
set the MissingItemsLimit property:

'==========================
Sub DeleteMissingItems2002()
'prevents unused items in XL 2002 PivotTable
Dim pt As PivotTable

Set pt = ActiveSheet.PivotTables.Item(1)
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone

End Sub
'=============================

For earlier versions, you can run the following macro:
'======================
Sub DeleteOldItemsWB()
'gets rid of unused items in PivotTable
' based on MSKB (202232)
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim i As Integer

On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
For Each pf In pt.VisibleFields
For Each pi In pf.PivotItems
If pi.RecordCount = 0 And _
Not pi.IsCalculated Then
pi.Delete
End If
Next
Next
Next
Next
End Sub
'================================
 
Debra
So I copied and pasted your code into the visual baisc editor, tried
to run it and got a Run-time error '438' Object doesn't support this
property or method.
On Debug the pt.PivotCache.Missing...etc line is highlighted.

Could you offer specific steps in addiing this code to mt worksheet so
that it eliminates ghost data from the drop down list. I am good with
spreadsheets (poweruser) but have never done any coding on them.

Thanks
Thomas
 
Thomas, are you using Excel 2002?

Debra

Mr.Ribit said:
Debra
So I copied and pasted your code into the visual baisc editor, tried
to run it and got a Run-time error '438' Object doesn't support this
property or method.
On Debug the pt.PivotCache.Missing...etc line is highlighted.

Could you offer specific steps in addiing this code to mt worksheet so
that it eliminates ghost data from the drop down list. I am good with
spreadsheets (poweruser) but have never done any coding on them.

Thanks
Thomas
Click to expand...
 
I think Debra was questioning if you used the macro for xl2002 or earlier
versions?


She gave you two options depending on your version.
 
Dave / Debra
You are absolutly right Debra did give me 2 options but I fixated on
the first option. Just tried the "older version" Debra and it worked
like a charm. THank you so much for your help.

Thomas
 
Thomas,

You're welcome! Thanks for letting us know that you fixed the problem.

Debra
 
Back
Top