How to clear data in pivot table?

[mcarter]

I have a pivot table that source data is always the same, but I delete
the old data and copy in new data. A field in the data and used in
the pivot table is county. When I copy in data for NY, refresh the pivot
table, delete the source data, and copy in new data for CT, and refresh
the pivot table, the counties for NY are still "seen" by the pivot table as
an option in the drop down for that field. How do I clear the pivot table
so that only
counties in CT are options to check or uncheck in the drop down?

I have tried refreshing the pivot table when the data source area is blank,
but the pivot table still shows all NY and CT counties as options in the
drop down.
If I copy another state's data in the area and resfresh, it keeps all
counties that
it has used previously and adds the new counties. I need it to "clean" the
pivot
table with each new data set as if the pivot table was newly set up.

 

[Gary Brown]

You didn't mention which version of Excel you are using.
Here are 2 macros, one is good for Excel 2002 and 2003 (2007 untested) and
the other is good from 97 - 2003 (2007 untested).

'/======================================/
'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
'/======================================/
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
'- Works with Excel 97 thru 2003
'- not tested w/ 2007
Dim i As Double, z As Double
Dim ptPivotField As PivotField
Dim ptPivotItem As PivotItem

On Error Resume Next

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
'/======================================/

 

[maha williams]

thank you, thank you, thank you!!!!! I have been trying for hours to clear out old names in my pivot table dropdown option. I have excel 2003 version and tried all the other tricks. The language you provided worked perfectly.

 

[Patti Newman]

I have a very similar situation. Except I want to append the current data
with new data.

I'm using Excel 2007 and have a pivot table report that is cumulative. Each
month I capture a new set of data for the table, paste it in the data tab,
but the pivot table only recognizes the original data set. In the previous
version of Excel, I could right click on the pivot table and rerun the Pivot
Table Wizard, and highlight the entire data set to update it, but this
feature is not available to me.

I have been painfully recreating my pivot table each time I add new data to
it.

Please help!
Patti

 

[Bernie Deitrick]

Patti,

I don't have 2007, but try inserting your new data within your old data
table instead of appending it on the end. Then just refresh your pivot
table. Since the data does not need to be sorted, you should be OK whether
you leave it alone or sort it after the insert.

HTH,
Bernie
MS Excel MVP