?
Solved

Set Pivot Table Filter in VBA from Control

Posted on 2012-09-20
5
Medium Priority
?
948 Views
Last Modified: 2012-10-03
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

0
Comment
Question by:fmcswill
  • 3
3 Comments
 

Author Comment

by:fmcswill
ID: 38428652
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
 

Accepted Solution

by:
fmcswill earned 0 total points
ID: 38459053
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
 

Author Closing Comment

by:fmcswill
ID: 38459063
Answered my own questions after lack of response from community.  Points go to me!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
I came across an unsolved Outlook issue and here is my solution.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question