I have an Excel 2010 workbook that creates an invoice from billing account and case information using index and index/match. I also have two pivot tables on the "Invoice" worksheet that I use to summarize invoice data from different worksheets. Each of these pivot tables have a couple of filter fields, although I want to update only one field (the same, and named the same on each pivot table) from a value that the user selects from a control box. Using some VBA code from various help forums I can get the pivot filter field to update, but it appears that previous results are not fully clearing or the pivot table filter is not resetting. So, despite the filter changing, my result set seems to sometimes reflect a different filter value. It almost appears as if the selected value is just overwriting the first visible value in the filter list, but not actually applying that value to the pivot table. Is there a refresh or clear cache function that would help here? I'm clearly missing something important here...
Thank you for any assistance you can provide.
Dim pt As PivotTable
Dim pf As PivotField
Dim DDFilter As String
Dim ActSheet As Worksheet
Set ActSheet = Sheets("Invoice")
On Error GoTo ErrorHandler
DDFilter = ActSheet.DropDowns("SelectContract").List(ActSheet.DropDowns("SelectContract").ListIndex)
'MsgBox "List index " & ActSheet.DropDowns("SelectContract").List(ActSheet.DropDowns("SelectContract").ListIndex)
For Each pt In ActSheet.PivotTables
pt.PivotFields("RMBnum").CurrentPage = DDFilter
ActSheet.PageSetup.PrintArea = "dSetPrintArea"
ErrorHandler: MsgBox Err.Description