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

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

Open in new window

fmcswillAsked:
Who is Participating?
 
fmcswillAuthor Commented:
I have answered my own question.  After reworking the VBA a little, I was still having trouble-essentially getting the opposite results of what I wanted.  I eventually tried renaming the data field (I had originally put a # sign in it) and added the renamed filter back to the pivot table.  This did the trick and everything seems to work just fine now.  Code below is for anyone who has a similar project in Windows 7, Excel 2010.  Function takes a single value selected from a control and then updates a specific pivot table filter on all pivot tables on a worksheet.

Sub PivotFilter()
 
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim ActSheet As Worksheet
Dim DDFilter As String
 
Set ActSheet = Sheets("Invoice")
DDFilter = ActSheet.DropDowns("SelectContract").List(ActSheet.DropDowns("SelectContract").ListIndex)

'MsgBox "Filtering Value is " & DDFilter
On Error GoTo ErrorHandler
 
For Each pt In ActSheet.PivotTables
    'MsgBox "Pivot Table is " & pt.Name
        
    For Each pi In pt.PivotFields("RMBnum").PivotItems
        If pi.Name = DDFilter Then
            pi.Visible = True
             'MsgBox "Pivot Filter using " & pi.Name & " is set"
        End If
    Next pi
 
    For Each pi In pt.PivotFields("RMBnum").PivotItems
        If pi.Name <> DDFilter Then
            pi.Visible = False
        End If
    Next pi
        pt.RefreshTable
Next pt
    ActSheet.PageSetup.PrintArea = "dSetPrintArea"
    Exit Sub
ErrorHandler:        MsgBox Err.Description
End Sub

Open in new window

0
 
fmcswillAuthor Commented:
Update to Question:
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
0
 
fmcswillAuthor Commented:
Answered my own questions after lack of response from community.  Points go to me!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.