Macro to Refresh Pivot Table AND Delete Old Items

[Jason K]

Hi,

I've used the following code every once in a while to delete old items
from the Pivot Tables I use.

Sub DeleteOldItemsWB()
'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


My question is can I include this in such a way that whenever the
Refresh button is hit, it automatically deletes the old items?
Currently, I have to refresh, then run the above macro. It would be
nice to perform it in one step.

Thanks.

Jason

 

[Dave Peterson]

Why do you refresh the pivottable manually?

Doesn't your macro do that anyway with this line: pt.RefreshTable.

(So your one step is to just run the macro!)