Refreshing pivot table drop down boxes

[Girard]

I have a pivot table that shows the last 6 months of
data. Every week I add a new week of data and remove the
oldest week of data. But since all I'm doing is
refreshing the pivot table, the drop-down boxes continue
to show data that is no longer available in the pivot
table (January 2003 is still showing in the drop down box
even though January data hasn't been included for months).

Is there any way to zero out the drop down boxes and have
them only show data that is currently in the report?

 

[Ken Wright]

Run the following code from Debra Dalgleish - It sits in my personal.xls, and
it's not moving for that very reason.

Sub PivotTableRefresh()
'Debra Dalgleish
'gets rid of unused items in PivotTable
' based on MSKB (Q202232)
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.PivotFields
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