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
'/======================================/
--
Hope this helps.
Thanks in advance for your feedback.
Gary Brown


"mcarter" wrote:

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.