Updating Pivot Tables

  • Thread starter Thread starter Steven Revell
  • Start date Start date
S

Steven Revell

Hi All,

I was wondering how to update a pivot table so that any
deleted data does not appear. The refresh command will add
any data not already in the table but it doesn't remove
data.

Any ideas?

Thanks,
Steven
 
From a previous post by Debra Dalgleish

Message-ID: <[email protected]>
Date: Fri, 22 Aug 2003 17:54:18 -0400
From: Debra Dalgleish <[email protected]>
Subject: Re: Pivot table filter list doesn't refresh
Newsgroups: microsoft.public.excel.programming



To eliminate the old items from the dropdowns, in Excel 2002, you can
set the MissingItemsLimit property:

'==========================
Sub DeleteMissingItems2002()
'prevents unused items in XL 2002 PivotTable
Dim pt As PivotTable

Set pt = ActiveSheet.PivotTables.Item(1)
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone

End Sub
'=============================

For earlier versions, you can run the following macro:
'======================
Sub DeleteOldItemsWB()
'gets rid of unused items in PivotTable
' based on MSKB (202232)
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.VisibleFields
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
'================================
 
Cheers Tom,

I'm running Office 97 and the code says its deleting the pivot items but
it doesn't seem to have any effect on the pivot table.

Cheers,

Steven Revell
Applications Development & Support
Covance Laboratories Europe, UK
 
I'm have deleted all the data in the source table and it still doesn't
seem to work. i took out the "on error resume next" and it just errors
everytime it tries to delete an item.

i'm really not sure why its not working, the sheet isn't protected.

Steven Revell
Applications Development & Support
Covance Laboratories Europe, UK
 
I'm have deleted all the data in the source table and it still doesn't
seem to work. i took out the "on error resume next" and it just errors
everytime it tries to delete an item.

i'm really not sure why its not working, the sheet isn't protected.

Steven Revell
Applications Development & Support
Covance Laboratories Europe, UK
 
This is some code from Stephen Bullen - it is essentially the same, but give
it a whirl: (it does exclude DATA, and doesn't have on error resume next -
so maybe that will make a difference).


Sub RemoveOldLabels()
Dim oPiv As PivotTable
Dim oField As PivotField
Dim oItem As PivotItem
Set oPiv = ActiveSheet.PivotTables(1)
For Each oField In oPiv.PivotFields
If oField.Name <> "Data" Then
For Each oItem In oField.PivotItems
If oItem.RecordCount = 0 And _
Not oItem.IsCalculated Then _
oItem.Delete
Next
End If
Next
End Sub
 
Back
Top