Macro to Refresh Pivot Table AND Delete Old Items
 

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

Macro to Refresh Pivot Table AND Delete Old Items
 

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



Jason K wrote:

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