Change Pivot Table Filter Based on Cell Value

Hi guys,

I have a problem with VBA I need to overcome.

OK, so, I have three Worksheets:

Data_Sheet – this contains a table of data with three columns; Date, Sales, Cost
Pivot_Sheet – this contains a pivot table of the data within the Data worksheet
Controls_Sheet – This contains a parameter which I would like to use to control the pivot table

What I would like to do is to change the date value in Controls_Sheet!B2, then run a macro, which would then filter all date values except the value within Controls_Sheet!B2.

So for example, if these are my dates within the ‘Dates’ column of my Pivot Table:

31/07/2010
07/08/2010
14/08/2010
21/08/2010
28/08/2010
04/09/2010

Then I set Controls_Sheet!B2 equals ‘04/09/2010’ then run the macro, only the line for ‘04/09/2010’ will be showing in the pivot table.

I have tried many codes all which seem not to work, so I haven’t posted them here. I have posted the example work sheet.

I’m sure this is simple buy I’m struggling with it. Thanks in advance.

The easiest thing to do is add an include column to your Data_Sheet Tab. The formula should be something like:

=IF(EXACT(Controls_Sheet!$B$2,""),TRUE,EXACT(Controls_Sheet!$B$2,Data_Sheet!B3))

You can then add this include field to the filter on your pivot table and set it to true. The macro can be set to run on any changes after you enter the value on the control sheet and the macro needs only to refresh the pivot table.

On a side note, Im not positive a pivot table is your best option. You could acheive all of this through formulas, thus avoiding the VBA altogether. All you need is a list of unique dates, then the same include criteria and SUMIFs. Pivot tables are intended for adhoc analysis, not necessary refreshable GUI

Thank you so much T-J – that works perfect!

One more thing; if I add ‘Cost’ as a filter to the Pivot Table, and I want to filter the spreadsheet based on cost, which is controlled from Controls_Sheet!B3, how do I do this?

Example spreadsheet attached.

This code should be independent from the first piece of code i.e. it will be run from a different macro, at a different time.

Thanks for the help here - really appreciated.

The following code will do that.
If the value in B3 isn't found in the filter items list, the current filter value will remain.

Sub Apply_Cost_Filter()
    Dim pvtTable As PivotTable
    Dim pvtField As PivotField
    Dim pvtItem As PivotItem
    Dim filterCost As String
    
    Set pvtTable = Worksheets("Pivot_Sheet").PivotTables("PivotTable1")
    Set pvtField = pvtTable.PivotFields("Cost")
    
    filterCost = Worksheets("Controls_Sheet").Range("B3")
    
    For Each pvtItem In pvtField.PivotItems
        If pvtItem.Value = filterCost Then
            pvtField.CurrentPage = filterCost
            Exit For
        End If
    Next pvtItem

End Sub

Please note the amendment to the first macro - the With pvtField.... End With lines aren't required.

TJ, I have a similar problem with a slight difference. I need 3 dates to be selected in the filter drop down. Is it possible to modify the macro so I can specify multiple date range on the controls_sheet, say from B2:B4? Any help will be much appreciated.

Thanks!