Macro to Refresh Pivot Table AND Delete Old Items

  • Thread starter Thread starter Jason K
  • Start date Start date
J

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
 
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!)
 
Back
Top