fmcswill
asked on
Set Pivot Table Filter in VBA from Control
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.
Will
Thank you for any assistance you can provide.
Will
Sub PivotFilter()
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.PivotCache.Refresh
pt.PivotFields("RMBnum").CurrentPage = DDFilter
pt.RefreshTable
Next
ActSheet.PageSetup.PrintArea = "dSetPrintArea"
Exit Sub
ErrorHandler: MsgBox Err.Description
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Answered my own questions after lack of response from community. Points go to me!
ASKER
I have posted my Excel workbook to see if this won't help facilitate a discussion or possible solution. The idea is that once some cases are ready to bill, the user sets the invoice increment manually (not necessarily one more than the previous) and clicks on the button at the top of the "Invoices" screen to store some essential details of the billing. That previous invoice information is then available for the next billing cycle as a reference to make sure we don't double bill or folks don't double pay. Again, the VBA that changes the filters does not seem to reliably change the filter information, even thought the filter appears to have changed.
Thank you in advance for any insights. Check out the function to set the print area while you are at it-I'm pretty happy with how this works.
File is Macro enabled Excel 2010.
Sample-Billing-Master.xlsm