Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Clear history from pivot table
Is there a way to remove items from previous versions of a pivot table for
which there is no longer data? Put another way, can the items that show up in the pull-down filters be limited just to data currently in the data source? (For example, I'm creating two versions of a pivot table for two different clients, but a field called "territory" contains a list of items for both clients, even if I replace the data set to include just one client.) Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Clear history from pivot table
'/=================================================/
Public Sub Pivot_Clear_Deleted_Data() 'get rid of items in PivotTable data that are no longer ' in the actual data but still shows in the dropdowns ' - affects all pivot tables on active worksheet Dim i As Double, z As Double Dim ptPivotField As PivotField Dim ptPivotItem As PivotItem i = ActiveSheet.PivotTables.Count If i 0 Then With ActiveSheet For z = 1 To i With .PivotTables(z) .RefreshTable For Each ptPivotField In .VisibleFields If UCase(ptPivotField.name) < "DATA" Then For Each ptPivotItem In ptPivotField.PivotItems If ptPivotItem.RecordCount = 0 Then If ptPivotItem.IsCalculated = False Then ptPivotItem.Delete End If End If Next ptPivotItem End If Next ptPivotField End With Next z End With End If End Sub '/=================================================/ 'from www.contextures.com/xlpivot04.html - Debbie Dalgleish '2005/02/23 'In Excel 2002, and later versions, you can 'programmatically change the pivot table properties, 'to prevent missing items from appearing, 'or clear items that have appeared. ' 'Sub DeleteMissingItems2002All() ''clears unused items in PivotTable ' ''in Excel 2002 and later versions ''If unused items already exist, ' 'run this macro then refresh the table 'Dim pt As PivotTable 'Dim ws As Worksheet ' 'For Each ws In ActiveWorkbook.Worksheets ' For Each pt In ws.PivotTables ' pt.PivotCache.MissingItemsLimit = xlMissingItemsNone ' Next pt 'Next ws ' 'End Sub '/=================================================/ HTH, -- Gary Brown If this post was helpful, please click the ''Yes'' button next to ''Was this Post Helpfull to you?''. "cchristensen" wrote: Is there a way to remove items from previous versions of a pivot table for which there is no longer data? Put another way, can the items that show up in the pull-down filters be limited just to data currently in the data source? (For example, I'm creating two versions of a pivot table for two different clients, but a field called "territory" contains a list of items for both clients, even if I replace the data set to include just one client.) Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert a row after a pivot table | Excel Discussion (Misc queries) | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
pivot table created from another pivot table | Excel Worksheet Functions | |||
Pivot table, dynamic data formula | Excel Discussion (Misc queries) | |||
Pivot Table Problems | Excel Discussion (Misc queries) |