Filtering Pivot Table In VBA

  • I am using the following code to filter my pivot table. It hides any data where the date is less than or greater than the specified dates.

    For Each PivItem In PivTable.PivotFields("Date").PivotItems
        If PivItem.Value < DateFrom Or PivItem.Value > DateTo Then
            If PivItem.Visible = True Then
                PivItem.Visible = False
            End If
        End If
    Next PivItem

    I also need to filter by my Account and Team fields. My attempt was to have three seperate blocks of the above code for the filtering criteria however when it gets to filtering the account it shows items outside of the date range.

    Is there anyway I can nest the above code so that it checks for the date, account and team all at once and then hide it if it doesn't match the criteria?



  • Re: Filtering Pivot Table In VBA

    Hi Matt,

    Have you got a sample workbook with the pivot table in it? Controlling a pivot table programmatically can be a pain at times.

  • Re: Filtering Pivot Table In VBA

    Hi Herbds7,

    Have you tried selecting the data items you want? I don't have Excel 2010 (Yet!)... When you select the items just logically think about the order you're selecting them and try and reflect them in the code??

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!