We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Excel VBA Multi select values in pivot page filter

Medium Priority
16,063 Views
Last Modified: 2012-05-11
I currently have a macro that performs a pivot selection based on defined criteria, updates a report with the filtered results, and then copies the report data into another workbook and saves each file .  This process is completed for approximately 22 files.  The problem I’m running into now is that I need to be able to select multiple values (40309, 40308, 30498, 30496) for Coid in the page/report filter.  I’ve tried several different statements, performing loops, etc. and none seem to do the multi select.  When doing the recorder I notice that it sets all the values for Coid to .visible=false but I’m unable to use this code as there can be new values added each month.  Here is a sample of my current code.  Is there a way in my Call Pvt_selection to include all 4 selections or using a loop to capture and to allow for values to fall in?  Code attached.


Sub GetFile()
   Call Pvt_selection(filname, "CONSOL - CALIFORNIA", "(All)", "(All)", "HMOG", "(All)")
   Call copy_values(filname, "CA", "HMO")

    Call Pvt_selection(filname, "CONSOL - ARIZONA", "(All)", "(All)", "RPPO", "(All)", "(All)")
    'Call copy_values(filname, "AZ", "RPPO")

    ActiveWindow.Close
    
    
End Sub

Sub Pvt_selection(fname As String, cm As String, bm As String, cf As String, prod As String, sp As String, cd As String)

   Windows(fname).Activate
   Application.Run "'" & fname & "'" & "!GoToPivot"
   'Sheets("pivot").Activate
   ActiveSheet.PivotTables("PivotTable1").PivotFields("Consolidated Market").CurrentPage _
           = cm
   ActiveSheet.PivotTables("PivotTable1").PivotFields("Base Market").CurrentPage _
           = bm
   ActiveSheet.PivotTables("PivotTable1").PivotFields("CMPLT_FACT_CD").CurrentPage _
           = cf
   ActiveSheet.PivotTables("PivotTable1").PivotFields("Product").CurrentPage _
           = prod
   ActiveSheet.PivotTables("PivotTable1").PivotFields("CO_SUBTYPE").CurrentPage _
           = sp
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Coid").CurrentPage _           = cd

Open in new window

Comment
Watch Question

Most Valuable Expert 2012
Top Expert 2012

Commented:
Perhaps this example will work, which demonstrates one way to make multiple pagefield selections.  If you hit the macro button, the app will select a State, Territory, and Director on this fictitious pivot table.


 
Sub UpdateFiltersAndPivots()
Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim pf As Variant

Application.ScreenUpdating = False

    'Make a selection change on a Pivot Table's Pivot Field to a new Pivot Item
    Set pt = Sheets("Pivot Data Table").PivotTables(1)
    
    'Change Territory to Houston
    'First, set the 3 levels to (ALL) just in case - to ensure a match

    For Each pf In pt.PageFields
        pf.CurrentPage = "(ALL)"
    Next pf
    
    'Now, set the PivotItem to focus on Houston on the Territory Pagefield
    
    For Each pf In pt.PageFields
        With pf
            Select Case .Name
                Case "State":
                    .CurrentPage = "Arkansas"
                Case "Territory":
                    .CurrentPage = "Little Rock"
                Case "Director":
                    .CurrentPage = "Carl"
                Case Else
                    'do nothing
            End Select
        End With
    Next pf
                
End Sub

Open in new window


See attached demo file,

Enjoy!

Dave
PlayingWithPivots-example-r1.xls
Most Valuable Expert 2012
Top Expert 2012

Commented:
Your approach - see alternative coding - should work as well:

 
Sub UpdateFiltersAndPivots_Version2()
Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim pf As Variant

Application.ScreenUpdating = False

    'Make a selection change on a Pivot Table's Pivot Field to a new Pivot Item
    Set pt = Sheets("Pivot Data Table").PivotTables(1)
    
    'Change Territory to Houston
    'First, set the 3 levels to (ALL) just in case - to ensure a match

    For Each pf In pt.PageFields
        pf.CurrentPage = "(ALL)"
    Next pf
    
    'Now, set the PivotItem to focus on Houston on the Territory Pagefield
    
    pt.PageFields("State").CurrentPage = "Arkansas"
    pt.PageFields("Territory").CurrentPage = "Little Rock"
    pt.PageFields("Director").CurrentPage = "Carl"
                
End Sub

Open in new window


Note, however, the code provided resets all pagefields to "(ALL)" before proceeding.  In addition, there's a presumption that the value being searched actually has data where the .CurrentPage is being selected.

You can test for this, before making the selection, by looping through all the pivot items, first.

That example is forthcoming on the next post.

Dave
PlayingWithPivots-example-r2.xls

Author

Commented:
Thank you Dave.

Is there a way to code for multiple values.  For example Territory Little Rock and Arkansas?
Most Valuable Expert 2012
Top Expert 2012
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Most Valuable Expert 2012
Top Expert 2012

Commented:
>>Is there a way to code for multiple values.  For example Territory Little Rock and Arkansas?

Ah - yes.  Example coming

Dave
Most Valuable Expert 2012
Top Expert 2012

Commented:
One moment - the example I gave does allow for multiple values - one value for each pagefield.

Do you mean multiple values like Texas and Arkansas?

That example, coming

Dave
Most Valuable Expert 2012
Top Expert 2012
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.