Routine to Delete Old PivotTable List Values

[Scott in Phoenix]

The fact that PivotTable Item Lists never update has bugged me forever. Well, the other day I got sick of it. We changed every value to upper case on a huge data sheet the other day. Rather than manually hide hundreds of Items, I got fed up and found the code below - which works!

How can I modify this code so it cleans every PivotTable in the active workbook? Right now, I have to change the worksheet name and the name of the PivotTable I want cleaned.

I could probably kluge something together and eventually get it to work. But I'd like something clean that I can add to my personal.xls and fire off anywhere anytime I want.

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

Thanks in advance!

Scott in Phoenix

 

[Scott in Phoenix]

Top

 

[Scott in Phoenix]

Top again

 

[pmdown]

GREAT !!!!!

This is better topic in whole year...

Thank You Scott...

 

[Scott in Phoenix]

Hope nobody minds me keepin' this one visible on the first page...

Topping again!

 

[pmdown]

There is Your code...

' This code deletes old items in all pivot tables in active workbook.
Sub Delete_Old_Pivot_Items()
On Error Resume Next
For Each WrkSheet In Worksheets
For Each PvtTable In WrkSheet.PivotTables
For Each PvtField In PvtTable.PivotFields
For Each PvtItem In PvtField.PivotItems
PvtItem.Delete
Next: Next: Next: Next
End Sub

 

[Scott in Phoenix]

Cool!

Hey - thanks for saving me all the trouble of trying to figure this out for myself. I'm at that state where I can see why your code works and how it does what it does, but have noooo clue how to make some of the things myself...

Thanks again!

Scott

 

[Ziggy12]

There's an oldy to be dug up.
I'm running Xl 2007 in compatability mode. I have a pivottable linked to external data in a Access data base.

The pivottable remembers previous feilds and data that has been replaced by new feilds and data by linking to a different database

Tried the code as suggested but it doesn't seem to work on all feilds.
Have I got the order of doing things wrong?
Run the code then refresh the pivot?

Any idea?

ziggy

 

[Krishnakumar]

See Clear Old Pivot Items

 

[Ziggy12]

Too easy.

Really good site to.
Also discovered that there were some comma's in my data that pushed everything along a couple of columns.

cheers
ziggy