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!

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!