Cannot refresh Pivot table List of Values 4

[MSIsam]

Using Excel 2003...
I have a pivot table that has a Row field for Territory. When I use the drop down menu, I see the territories that I can choose from. The problem is that I am seeing old values in teh dropdown list and I cannot get rid of them. The old values do not exist on the source data. I have tried refreshing the pivot table as well as pulling the field of and putting back on. Any ideas on how to get rid of these old values?

Any help would be greatly appreciated,
Sam

 

[Scott02]

If the pivot table isn't linked to anything or too complex, I'd suggest deleting the sheet and recreating it.

Not sure why refresh isn't working. Are there any hidden rows/columns in the data area that may still be feeding it?

 

[MSIsam]

Thanks Scott,
The pivot has a lot of formatting and I do not want to delete it. Furthermore, I am using the same spreadsheet to create multiple reports. I run for 1 region then save, run for another and save, etc. This is where the old values are coming from, eventhough the raw data is overwritten. The list of values must be cached somewhere, I just can't get to it.

 

[pbrodsky]

I think I remember seeing a VBA routine in this forum (or maybe the VBA forum) for clearing old values from a pivot table cache. I don't remember any of the details though.

It might be worthwhile trying an internet search.

 

[KenWright]

Only one place you need for this stuff, and that's Debra's site

Sub PivotTableRefresh()
'Debra Dalgleish
'gets rid of unused items in PivotTable
' based on MSKB (Q202232)
    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.PivotFields
                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

Regards
Ken................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Tianjin]

I had exact the same problem as you have encountered. I still think Excel itself should have something in there simply to deal with the cumbersome. I just counldn't find it anywhere. instead, I wrote following code. It may run very slow for those large and long run pivottable, but it do work.

Sub CleanPivotItemList()
Dim PvtTable As PivotTable
Dim Pvtfield As PivotField
Dim PvtItem As PivotItem
Dim sh As Worksheet
Set sh = ActiveSheet
Application.ScreenUpdating = False
For Each PvtTable In sh.PivotTables
For Each Pvtfield In PvtTable.RowFields
On Error Resume Next
For Each PvtItem In Pvtfield.PivotItems
PvtItem.Delete
Err.Clear
Next
On Error GoTo 0
Next
Next
EndPoint:
Application.ScreenUpdating = True
End Sub

 

[MSIsam]

Thanks Guys,
Unfortunately, I get errors when running either code
For Tianjin, I get an "Application-defined or object-defined error. Debugging highlights eht following code:

Sub CleanPivotItemList()
    Dim PvtTable As PivotTable
    Dim Pvtfield As PivotField
    Dim PvtItem As PivotItem
    Dim sh As Worksheet
    Set sh = ActiveSheet
    Application.ScreenUpdating = False
    For Each PvtTable In sh.PivotTables
        For Each Pvtfield In PvtTable.RowFields
            On Error Resume Next
            For Each PvtItem In Pvtfield.PivotItems
               [COLOR=red] PvtItem.Delete [/color]
                Err.Clear
            Next
        On Error GoTo 0
        Next
    Next
EndPoint:
    Application.ScreenUpdating = True
End Sub

I am not VBA Savvy so apologies for my ignorance.
Ken - I ran your code but it froze up the machine and then returned an error (forgot to record)

Thanks Again

 

[Tianjin]

The code stopped at highlight during you run it?

You can just run through the code by using F5 key rather than steping it through with F8 key.
The code shouldn't stop with the line "On error resume next" prior to the "PvtItem.Delete" even through "PvtItem.Delete" may generate an error when it try to delete an existing territory. I run this perfectly in my excel 2000, but I don't think it would have any problem with upper version of excel.

 

[KenWright]

Did you try the KB mentioned in the code. Try the routine they suggest, using appopriate names for your sheet and table:-

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q202232

Sub Delete_Fields()
   On Error Resume Next
   For Each pvtfield In Worksheets("X").PivotTables("Y").PivotFields
      For Each pvtitem In pvtfield.PivotItems
         pvtitem.Delete
      Next
   Next
   ActiveSheet.PivotTables("Y").RefreshTable
End Sub

Regards
Ken.........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[MSIsam]

Thanks Guys,
That did it!

Much appreciated!
Sam

 

[KenWright]

You're welcome

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[xlbo]

no need for all that malarky guys - just need to se the MissingItemsLimit of the pivotcache

For each pt in thisworkbook.pivottables
 pt.pivotcache.MissingItemsLimit = xlMissingItemsNone
Next

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question