Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create refresh button in worksheet to refresh Pivot Table Data | Excel Worksheet Functions | |||
refresh a new worsheet on pivot table refresh | Excel Worksheet Functions | |||
pivot table refresh macro | Excel Discussion (Misc queries) | |||
Can you reformat order of items in Pivot Tables after refresh? | Excel Discussion (Misc queries) | |||
Pivot Table REFRESH Flaw -- Saves Old Data in Selection Area AFTER REFRESH | Excel Programming |