Incorrect Field Names Appear in PivotTable Field Dialog Box

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
0
tbende (9)
9/25/2003 3:01:32 PM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
1072 Views

Similar Articles

[PageSpeed] 22

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
'================================



Mr.Ribit wrote:
> 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


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd (439)
9/27/2003 1:32:45 AM
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


Debra Dalgleish <dsd@contextures.com> wrote in message news:<3F74E8BD.8050606@contextures.com>...
> 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
> '================================
> 
> 
> 
> Mr.Ribit wrote:
> > 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
0
tbende (9)
9/30/2003 5:52:44 PM
Thomas, are you using Excel 2002?

Debra

Mr.Ribit wrote:
> 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
> 
> 
> Debra Dalgleish <dsd@contextures.com> wrote in message news:<3F74E8BD.8050606@contextures.com>...
> 
>>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
>>'================================
>>
>>
>>
>>Mr.Ribit wrote:
>>
>>>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
>>


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
9/30/2003 6:16:01 PM
No 2000, SP1

Debra Dalgleish <dsd@contexturesXSPAM.com> wrote in message news:<3F79C861.50608@contexturesXSPAM.com>...
> Thomas, are you using Excel 2002?
> 
> Debra
> 
> Mr.Ribit wrote:
> > 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
> > 
> > 
> > Debra Dalgleish <dsd@contextures.com> wrote in message news:<3F74E8BD.8050606@contextures.com>...
> > 
> >>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
> >>'================================
> >>
> >>
> >>
> >>Mr.Ribit wrote:
> >>
> >>>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
> >>
0
tbende (9)
10/1/2003 3:55:13 PM
I think Debra was questioning if you used the macro for xl2002 or earlier
versions?

> > >
> > >>To eliminate the old items from the dropdowns, in Excel 2002, you can
> > >>set the MissingItemsLimit property:
> > >>Sub DeleteMissingItems2002()

> > >>For earlier versions, you can run the following macro:
> > >>'======================
> > >>Sub DeleteOldItemsWB()


She gave you two options depending on your version. 

"Mr.Ribit" wrote:
> 
> No 2000, SP1
> 
> Debra Dalgleish <dsd@contexturesXSPAM.com> wrote in message news:<3F79C861.50608@contexturesXSPAM.com>...
> > Thomas, are you using Excel 2002?
> >
> > Debra
> >
> > Mr.Ribit wrote:
> > > 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
> > >
> > >
> > > Debra Dalgleish <dsd@contextures.com> wrote in message news:<3F74E8BD.8050606@contextures.com>...
> > >
> > >>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
> > >>'================================
> > >>
> > >>
> > >>
> > >>Mr.Ribit wrote:
> > >>
> > >>>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
> > >>

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
10/2/2003 12:02:47 AM
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

Dave Peterson <ec35720@msn.com> wrote in message news:<3F7B6B27.B2795305@msn.com>...
> I think Debra was questioning if you used the macro for xl2002 or earlier
> versions?
> 
> > > >
> > > >>To eliminate the old items from the dropdowns, in Excel 2002, you can
> > > >>set the MissingItemsLimit property:
> > > >>Sub DeleteMissingItems2002()
>  
> > > >>For earlier versions, you can run the following macro:
> > > >>'======================
> > > >>Sub DeleteOldItemsWB()
> 
> 
> She gave you two options depending on your version. 
> 
> "Mr.Ribit" wrote:
> > 
> > No 2000, SP1
> > 
> > Debra Dalgleish <dsd@contexturesXSPAM.com> wrote in message news:<3F79C861.50608@contexturesXSPAM.com>...
> > > Thomas, are you using Excel 2002?
> > >
> > > Debra
> > >
> > > Mr.Ribit wrote:
> > > > 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
> > > >
> > > >
> > > > Debra Dalgleish <dsd@contextures.com> wrote in message news:<3F74E8BD.8050606@contextures.com>...
> > > >
> > > >>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
> > > >>'================================
> > > >>
> > > >>
> > > >>
> > > >>Mr.Ribit wrote:
> > > >>
> > > >>>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
> > > >>
0
tbende (9)
10/3/2003 3:43:26 PM
Thomas,

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

Debra

Mr.Ribit wrote:
> 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
> 
> Dave Peterson <ec35720@msn.com> wrote in message news:<3F7B6B27.B2795305@msn.com>...
> 
>>I think Debra was questioning if you used the macro for xl2002 or earlier
>>versions?
>>
>>
>>>>>>To eliminate the old items from the dropdowns, in Excel 2002, you can
>>>>>>set the MissingItemsLimit property:
>>>>>>Sub DeleteMissingItems2002()
>>>>>
>> 
>>
>>>>>>For earlier versions, you can run the following macro:
>>>>>>'======================
>>>>>>Sub DeleteOldItemsWB()
>>>>>
>>
>>She gave you two options depending on your version. 
>>
>>"Mr.Ribit" wrote:
>>
>>>No 2000, SP1
>>>
>>>Debra Dalgleish <dsd@contexturesXSPAM.com> wrote in message news:<3F79C861.50608@contexturesXSPAM.com>...
>>>
>>>>Thomas, are you using Excel 2002?
>>>>
>>>>Debra
>>>>
>>>>Mr.Ribit wrote:
>>>>
>>>>>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
>>>>>
>>>>>
>>>>>Debra Dalgleish <dsd@contextures.com> wrote in message news:<3F74E8BD.8050606@contextures.com>...
>>>>>
>>>>>
>>>>>>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
>>>>>>'================================
>>>>>>
>>>>>>
>>>>>>
>>>>>>Mr.Ribit wrote:
>>>>>>
>>>>>>
>>>>>>>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
>>>>>>


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
10/3/2003 6:12:15 PM
Reply:

Similar Artilces:

minimising a dialog
Hi, I want to minimise my dialog, but i've removed the minimize / maximise buttons. I'm going to use one of my own buttons, but I don't know how to call the minimisation. I've removed the exit button, but have created my own one instead. However, one draw back of removing the title bar etc is that in the system tray thing (not sure what it's called) at the bottom middle of my screen, with most applications it shows the name, and then a right click will give the optinos of closing, maximising etc. Anyway, these are now not there. How do I fix this? Any help much appreciat...

Multiple user names at one bank or brokerage
I have 3 accounts at one bank. I have 3 corresponding accounts set up in MS Money 2007. One of them is setup for online banking while the other two are not. I now want to set the other two up for online banking but I can't find a way to specify the unique login info for each of the other two accounts. ...

I have a red pencil box
It's red and holds pencils. ...

When hitting reply to a message, no "Bcc:" header field appears
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: Exchange Hi. When I hit &quot;Reply&quot; or &quot;Reply All&quot; to a message in Entourage, the composition window that appears has the usual &quot;To:&quot;, &quot;Cc:&quot;, and &quot;Subject:&quot; form fields near the top, but not one for &quot;Bcc:&quot;. I've looked all over Preferences but don't see an option for this. What am I missing? <br><br>Thanks, <br><br>-c On 2/26/10 11:51 AM, cmetzler@officeforma...

Excel Data appearing in new Pop-up window rather than frameset frame
I have a servlet running in Tomcat that delivers an excel data file, the response content type being set to: "application/vnd.ms-excel" My html page uses a frameset to display (amongst other things) the excel data. The data is displayed on the click of a button and is directed to a particuler frame: <FORM method=post action="servlet/XLDataServlet/data.xls" target="exceldata"> <INPUT TYPE=SUBMIT VALUE="Display Excel Data"> </FORM> With Internet Explorer 6.0 the excel data is displayed in the target frame, with Internet Explorer 5.5...

Updating Boolean Field with onChange
I am having difficulty with setting a value of a new boolean field using the OnChange scripting against a picklist. If anyone has some sample code / formatting information that I can use it would be greatly appreciated. Also, I also need to know if it is possible to stop the default value for the Revenue (isrevenuesystemcalculated) field on the opportunity form being set to "Yes" when the "Convert Lead" function is used. We can set the default value to "No" for user created Opportunities but when Convert Lead is used it seems to ignore the default value. Again, a...

Non-modal dialog pParent initialization (and warning C4355)
I have read several posts about "warning C4355: 'this' : used in base member initializer list", and I understand about the object of 'this' not being fully initialized, but that it is common 'safe' practice to use this method to only save a pointer for future use.. OK, my question is; What is the proper or best way to initialize a non-modal dialog? I have used two methods: 1.) CFooDlg::CFooDlg(CWnd* pParent /*=NULL*/) : CDialog(IDD, pParent) { // pParent is not set here, See Create pCV=NULL; } BOOL CFooDlg::Create(CWnd * pParent) { pCV=(CMyBar ...

instructions disppear when users begin type (text field)
Hi all, I need to customize the outlook contact form and I want to add one text field to allow users to add details info and instruct users how to add. Instructions shows in the field and the instructions disappear when users click and begin to type. How should I do this? exchange 2003/outlook2003 Thank you. It's hard for me to visualize exactly what you're expecting to happen. If you want the instructions to stay on the screen, you could display them in a label control. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook 2007 Programming: Jumps...

Fields
I am working in an education template and want to see if there is a way to create a field that can be added to periodically to show all the courses a person takes from my instructors. I created a field named course but it is only allowing me to put one course in. How can I make the field list all the courses? Thanks, Nantika -- Enjoy every day! On Sat, 17 Apr 2010 09:18:01 -0700, Nantika <Nantika@discussions.microsoft.com> wrote: >I am working in an education template and want to see if there is a way to >create a field that can be added to periodically ...

Inbox and unread box
What's the purpose of having an inbox and an unread box in my outlook 2003. They both read the same number of messages. Is it a duplicate? With Outlook xp, it would simply show a number in black, to let me know the number of unopened messages. Now, there seems to be a separate folder entitled "unread" and it has the exact same number of email messages as my inbox ... all of time, and has always displayed the exact same number since I installed it. Is anyone able to explain this? I dont' understand it. Thanks in advance. Diana Definitely not a duplicate; it's a S...

Dialog displayed
Hi I'm creating a dialog and calling it with domodal(). Is there a message handler or something insode my dialogclass that is called after the dialog is displayed? Thanks Urban Yes & No. Take a look at the OnInitDialog() message handler. OnInitDialog() gets called before the dialog is displayed. However you can post a user-defined message to yourself from the OnInitDialog() handler, and handle things that you want to do after the dialog is displayed. -- Cheers Check Abdoul [ VC++ MVP ] ----------------------------------- "Urban Stadler" <mail@ustadler....

Reports and making it look prettier: Last Name, First Name Rank
I'm trying to pretty up my report by eliminating the forced space created by having one field of the report for 'LastName', one for 'FirstName', and one for 'Rank.' The Rank isn't too huge of an issue, and if three items in one field gets to be too much, I have no problem leaving that as a side item of sorts. But, I want my report to look a bit better by putting the names together! I want the report to go to my table, pull the LastName from that column, and pair it with the matching FirstName in the column to the right. (Since it's just...

Preveting the business fax appearing as a contact
When I select a name to send an e-mail to, I type in part of the name, press Alt K and the contacts matching that partial name appears. Unfortunately, it gives me two choices for anyone with a fax number. I don't fax from e-mail. Short of modifying the details for hundreds of contacts, is there a way to turn off this "feature" so fax contacts aren't showing up? Thanks No. Outlook considers fax numbers to be valid electronic addresses, since there are many client- and server-based components that can use such addresses. One method to hide fax numbers from the address b...

Name Range with using Data Form
I first created a variable with A1:D1, four columns with named variable Data. How to expand this Data range to A1:D2 after using the DataForm to add one row data in the worksheet?? The same, the Data range should be A1:D3 when adding one more data row... million thanks Hi use the following formula in the name definition dialog =OFFSET($A$1:$D$1,0,0,COUNTA($A:$A)) >-----Original Message----- >I first created a variable with A1:D1, four columns with named variable >Data. How to expand this Data range to A1:D2 after using the DataForm to add >one row data in the worksheet?? The...

Default value for custom field?
How can I populate a custom field with a default value? Specifically, I have a custom field "DisplayName" associated with the Quote Detail object. I want initially to populate this field with the value of the product name field when a new product is added to a quote. The user can then edit the DisplayName custom field if desired. The DisplayName custom field will be used as the product name on a Crystal Reports quote form. ...

assign cell content as file name
I am attempting to create a macro that will save a workbook with the file name under which I save the workbook coming from the contents of one of the cells in the workbook. Suppose that each time the workbook was altered, a specific cell (A1 on Sheet1 for instance) contained the desired file name for that workbook. It is my goal to create a macro that automatically assigns said workbook a name based on the content of that cell. Any help would be most appreciated. Something like??? dim myFileName as string myfilename = "C:\temp\" & thisworkbook.worksheets("sheet1"...

Recipient appears twice in the message header
Hi, I have a user with Outlook 2000 set up as IMAP. She received a few e-mails yesterday where she appears in the recepient(TO) field 2 or 3 times, but the sender has only put her address once. I have checked the header in the raw MSG file and her address only appears once. What could cause this? How do I fix this? Please advise. The user is driving me nuts with this. Thanks, Ed ...

Text box jumps to left of page
Word 2004 (I am relatively new to Word and am delighted to find a forum specifically for the Mac version. There are a number of unresolved, niggling issues I can live however they slow the workflow. I am eager to learn.) In the recent past, I manually converted 12,000+ recipes from WordPerfect 7 to Word. Since Word 2004 does not have a filter for the old files, the conversion was done on the Windows side of my Mac in Word2003. Those files _usually_ open without protest also in 2004. One annoyance regards text boxes. When text was highlighted and a text box was requested for it in...

modify description field in reports
When I print my PO's I can make the part # field in the view report screen wider but when i print it, it defaults back to the regular size. The problem is it cuts off my part numbers on some of my long numbers. Is there a way to overcome this? you must modify the po.xml file directly. "www.choppersapparel.com" <www.choppersapparel.com@discussions.microsoft.com> wrote in message news:8B4E31D5-10A7-4A99-913D-F8F6E3BD7043@microsoft.com... > When I print my PO's I can make the part # field in the view report screen > wider but when i print it, it defaults ...

How did you add text into publisher, without using boxes?
how do you add text to publisher without using text boxes I suppose you could create your text as an image and insert the image into your publisher file. -- Don Vancouver, USA "Calvin Scott" <Calvin Scott@discussions.microsoft.com> wrote in message news:64D23D52-138D-47B4-B265-4A41BF14BF55@microsoft.com... > how do you add text to publisher without using text boxes Calvin Scott <Calvin Scott@discussions.microsoft.com> was very recently heard to utter: > how do you add text to publisher without using text boxes You don't. Text in Publisher has to e...

How do you change a field name in 2002 Excel
I have copied and pasted a whole database from Works into the Excel program but can't seem to find a way to change the field names from A, B, C, to what I want as Last Names, First Names, etc. Aarrrrgh. It can't be THAT difficult! <G> Using the HELP did nothing for me thus I am here asking this silly Q. AnnE in MN You cannot change the Column letters from A, B, C etc. You can choose to have column and row headers not shown under Tools>Options>View Enter your titles(names) in row 1 then select A2 and Window>Freeze Panes to lock row 1 in view. Gord Di...

Multiple indexes on same fields in different order
I sometimes need to find the largest or smallest value in a field. If I put two indexes on the same field, one in ascending order and one in descending order, is Jet smart enough to use the proper index if a give it a grouping query with the condition Max or Min, or am I just wasting resources? Pete -- This e-mail address is fake, to keep spammers and their address harvesters out of my hair. If you want to get in touch personally, I am 'pdanes' and I use yahoo mail. But please use the newsgroup when possible, so that all may benefit from the exchange of ideas. ...

Incorrect quantity transferred from SOP order to invoice
What's the correct way to fix an incorrect quantity that was transferred to an invoice? For example, say we have an order with many line items where half of the items were fulfilled, some were partial-shipped. When the order transfers to an invoice, we realize that the fulfilled quantity was wrong on one of the items. We can change the QTY Fulfilled and QTY to Invoice on the invoice but how do we fix the order so that the difference doesn't transfer again? Do we go into the order and enter the difference in Cancelled QTY, although that would be misleading when an inquiry is ...

List box
I am trying to use the list box function from the control toolbar. I am not sure how to get the listings I want in the list box. Is there certain VB coding that is needed? Any help will be appreciated. Thanks. Todd No VBA code needed. In Design mode, click on your list box and select Properties from the Control toolbox (or right-click your list box and select Properties), then scroll down to the ListFillRange Property and indicate the cell range address that has the items you want to appear in the box (i.e., A1:A10). Then exit out of design mode and test your list box. MRO "Tod...

Table headers in a list box (Custom Report)
I have a big table with many fields (more than fifty). They are not in one table but related. Now user's come with a requirement of a separate type of report everyday which has different fields. because of which i have to design a report for them with the required query. I was therefore wondering can i make a form which has two list boxes the one on left side will have all the fields of the table (only headers needed, not data). Users can select multiple no. of fields from that list box which will appear in another list box. Users should be able to then select the order of fi...