Clear pivot table filters

[Turbo613]

Overview
I have 7 pivot tables on one workbook in Excel2003. I have a combo box that updates all the pivot tables at the same time. The combo box references a named range via an index function. The lookup table that the index function and combo box uses is set as a named range that can expand or contract; im not sure if that is important, but here is the code I have for the named range called "Office2" =OFFSET(Pivot!$L$100,0,0,COUNT(Pivot!$L:$L),1).

Problem
Most of the time the pivots work when I use the dropdown box. but on ocassion, they don't update to the new data from the combo box. I think I need to use a feature that is available in Excel2007 called ClearAllFilters, but I don't know what that is called or how to do it in Excel2003 VBA coding and I need help.

Code
Here is the module the Combo box references:

Sub ProPivotCombo()

ActiveSheet.PivotTables("Pvt1").PivotFields("OfficeNumber").CurrentPage = Range("N98").Text

ActiveSheet.PivotTables("Pvt2").PivotFields("OfficeNumber").CurrentPage = Range("N98").Text

ActiveSheet.PivotTables("Pvt3").PivotFields("OfficeNumber").CurrentPage = Range("N98").Text

ActiveSheet.PivotTables("Pvt4").PivotFields("OfficeNumber").CurrentPage = Range("N98").Text

ActiveSheet.PivotTables("Pvt5").PivotFields("OfficeNumber").CurrentPage = Range("N98").Text

ActiveSheet.PivotTables("Pvt6").PivotFields("OfficeNumber").CurrentPage = Range("N98").Text

End Sub[/b]

 

[Datsmart]

This code will clear old data from all your pivot tables:

Sub Clean_Pivots()
Dim ws As Workbook
Dim pt As PivotTable
    For Each ws In Workbooks
        For Each pt In ActiveSheet.PivotTables
            pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
        Next
    Next
End Sub

 

[Turbo613]

Perfect, thanks very much.