locked
Pivot Table VBA Filters RRS feed

  • Question

  • Hey everyone,

    I have created a VBA code that lets you select a "Country" from a dropdown menu list. This then filters 3 pivot tables (can have more pivots if needed), to show the country selected.

    Now, im wondering if I can add in a second filter. Say "Product Type". So the final result will be if I select "Spain" and "Cars" for example the VBA will filter all pivot tables in the worksheet to show a filter on Spain Country and Cars Product Type.  Can someone help with how to adapt the VBA Coding I already have for the country?

    Im very new to VBA so any help would be appreciated and maybe an explanation as to "how" its working. I ve heard people talking about loops etc, and I have no idea! Its not needed yet, but is it also possible to add in more filters if needed and then how do I adapt the code? Is it as simple as "copying, pasting and changing elements".


    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pi As PivotItem
    Dim strField As String

    strField = "Target Country Data"

    On Error Resume Next
    Application.EnableEvents = False
    Application.ScreenUpdating = False

        If Target.Address = Range("SelRegion").Address Then
           
    '        For Each ws In ThisWorkbook.Worksheets
            Set ws = Me
                For Each pt In ws.PivotTables
                    With pt.PageFields(strField)
                        For Each pi In .PivotItems
                            If pi.Value = Target.Value Then
                                .CurrentPage = Target.Value
                                Exit For
                            Else
                                .CurrentPage = "(All)"
                            End If
                        Next pi
                    End With
                Next pt
    '        Next ws
       
        End If

    Application.EnableEvents = True
    Application.ScreenUpdating = True

    End Sub


    • Edited by InzieBear Wednesday, August 31, 2016 11:39 AM
    Wednesday, August 31, 2016 11:38 AM

Answers

  • Just copy the With pt.PageFields... like below.

    Loop is for executiing same instruction many times.

    The repeatation count may be fixed. Say repeat 10 times then us "For i=1 to 10.....next i"

    The repeatation may be over a collection of objects. Like below. "For Each ws in Worksheets... next ws"

    The repeatation may be based on some condition. Then "Do While....Loop", Do...Loop While" etc.

    You can check many good books on VBA

    In Pivottable the Report Filter is PageFields Object, Row Label  is  RowFields, Column Labels is COlumnFields, Values is DataFields. And a general field is PivotFields.

    For Each pt In ws.PivotTables
                        'First Page Field
                        With pt.PageFields(strFieldOne)
                            For Each pi In .PivotItems
                                If pi.Value = Target.Value Then
                                    .CurrentPage = Target.Value
                                    Exit For
                                Else
                                    .CurrentPage = "(All)"
                                End If
                            Next pi
                        End With
                        
                        '2nd Page Field
                        With pt.PageFields(strFieldTwo)
                            For Each pi In .PivotItems
                                If pi.Value = Target.Value Then
                                    .CurrentPage = Target.Value
                                    Exit For
                                Else
                                    .CurrentPage = "(All)"
                                End If
                            Next pi
                        End With
                                            
                        
                    Next pt
    



    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    • Proposed as answer by David_JunFeng Thursday, September 8, 2016 1:26 AM
    • Marked as answer by David_JunFeng Thursday, September 8, 2016 1:26 AM
    Wednesday, August 31, 2016 3:30 PM
    Answerer

All replies

  • Just copy the With pt.PageFields... like below.

    Loop is for executiing same instruction many times.

    The repeatation count may be fixed. Say repeat 10 times then us "For i=1 to 10.....next i"

    The repeatation may be over a collection of objects. Like below. "For Each ws in Worksheets... next ws"

    The repeatation may be based on some condition. Then "Do While....Loop", Do...Loop While" etc.

    You can check many good books on VBA

    In Pivottable the Report Filter is PageFields Object, Row Label  is  RowFields, Column Labels is COlumnFields, Values is DataFields. And a general field is PivotFields.

    For Each pt In ws.PivotTables
                        'First Page Field
                        With pt.PageFields(strFieldOne)
                            For Each pi In .PivotItems
                                If pi.Value = Target.Value Then
                                    .CurrentPage = Target.Value
                                    Exit For
                                Else
                                    .CurrentPage = "(All)"
                                End If
                            Next pi
                        End With
                        
                        '2nd Page Field
                        With pt.PageFields(strFieldTwo)
                            For Each pi In .PivotItems
                                If pi.Value = Target.Value Then
                                    .CurrentPage = Target.Value
                                    Exit For
                                Else
                                    .CurrentPage = "(All)"
                                End If
                            Next pi
                        End With
                                            
                        
                    Next pt
    



    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    • Proposed as answer by David_JunFeng Thursday, September 8, 2016 1:26 AM
    • Marked as answer by David_JunFeng Thursday, September 8, 2016 1:26 AM
    Wednesday, August 31, 2016 3:30 PM
    Answerer
  • Hi InzieBear,

    According to your description, you could use "Record Macro" to get VBA code. The Macro Recorder, a very useful tool included in Excel VBA, records every task you perform with Excel. All you have to do is record a specific task once. Next, you can execute the task over and over with the click of a button. The Macro Recorder is also a great help when you don't know how to program a specific task in Excel VBA. Simply open the Visual Basic Editor after recording the task to see how it can be programmed.

    In addition could you upload your Excel file on OneDrive, that will help us reproduce and resolve your issue.

    Thanks for your understanding.
    Thursday, September 1, 2016 3:10 AM