Scott in Phoenix
New Member
- Joined
- Mar 5, 2002
- Messages
- 13
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.
Thanks in advance!
Scott in Phoenix
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.
Code:
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