Filtering multiple pivot tables based on drop down selections using VBA

Question

I'm hoping someone can help. I have a dashboard created by someone else where there are numerous tables across sheets all operating from drop down date selections (From - To) on sheet 1. I have been asked to add to this, and have created pivot tables that are most suitable to the work. The problem I have is that I need them to filter based on the drop down dates on sheet 1.

I am hoping this is possible via VBA.

I have been able to get my pivot reports to filter based on another drop down which is text based. But cannot get the same code (when tweaked to focus on the "month" option and associated drop down cell) to work for a date selection, and I also cannot figure out how to allow multiple selections so that I can choose the date range.

The code I have been using is as follows:

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 = "Region"

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

If Target.Address = Range("D2").Address Then

    For Each ws In ThisWorkbook.Worksheets
        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

Any help anyone is able to provide would be much appreciated. I'm fairly new to VBA and trying my best to tweak code I am finding online but struggling.

Thanks

Revised: I also tried the below code that I found elsewhere which was being used for selecting date ranges

Sub FilterPivotDates()
'
Dim dStart As Date
Dim dEnd As Date
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

Application.ScreenUpdating = False
On Error Resume Next

dStart = Sheets("Pivots").Range("F2").Value
dEnd = Sheets("Pivots").Range("f3").Value

Set pt = ActiveSheet.PivotTable1
Set pf = pt.PivotFields("Month")

pt.ManualUpdate = True

pf.EnableMultiplePageItems = True

For Each pi In pf.PivotItems
  pi.Visible = True
Next pi

For Each pi In pf.PivotItems
 If pi.Value < dStart Or pi.Value > dEnd Then
pi.Visible = False
End If
Next pi

Application.ScreenUpdating = False
pt.ManualUpdate = False

Set pf = Nothing
Set pt = Nothing

End Sub

In the example I found this was being operated by a button but I just tried it in the sheet. But this also does not work for me.

Answer 1

Here's a couple of suggestions.

1. What version of Excel do you have? You may be able to simply sidestep the issue if using Excel 2010 or later, because you can simply set up a Slicer on the date field, and then connect that Slicer to any PivotTables that you want to sync. In later versions you can do the same using a specialised slicer called a Timeline. I wrote a related post on this some time back at the following link that might be of interest: http://dailydoseofexcel.com/archives/2014/08/16/sync-pivots-from-dropdown/

2. According to IronyAaron in the comments at this thread:

   When VBA recognizes the dates in the pivot cache, it reads the US version after parsing although the item is read as a locally formatted string. This therefore causes VBA to fail when recognizing Date variables.

That might be your issue. I recently experienced some similar issues and got around it by converting the date I wanted to filter the Pivot on to a DateSerial (to get around US vs Non-US date 'incompatibilities') and then cast that DateSerial as a Long, with the following line of code:

CLng(DateSerial(Year(vItem), Month(vItem), Day(vItem)))

Your code may need something like this. If you amend your question to include the exact code that isn't working then I'll take a look.

3. Check out this recent SO question I answered: It might be easily amended to do what you want. Filtering pivot table with vba

---Edit---

The most efficient way to filter pivots programmatically on a date range is to leverage the inbuilt Date Between functionality, i.e. this thing:

The only problem is that this functionality isn't available to PageFields (i.e. fields dragged to the Filters pane) as per the following screenshot:

So if you want to use the following code, you'll have to drag your Month fields into the PivotTable as a RowField, like so:

Assuming that's not going to present any issues, then you can use the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim pt As PivotTable
Dim vItem As Variant
Dim rFrom As Range
Dim rTo As Range

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With

Set rFrom = Range("dvFrom")
Set rTo = Range("dvTo")

If Not Intersect(Target, rFrom) Is Nothing Or Not Intersect(Target, rTo) Is Nothing Then
    If rFrom < rTo Then
        For Each vItem In Array("PivotTable1", "PivotTable2") 'Change PivotTable names as appropriate
            Set pt = Sheet1.PivotTables(vItem) 'Change Sheet as appropriate
            With pt.PivotFields("Month")
                .ClearAllFilters
                .PivotFilters.Add2 _
                    Type:=xlDateBetween, _
                    Value1:=CLng(DateSerial(Year(rFrom), Month(rFrom), Day(rFrom))), _
                    Value2:=CLng(DateSerial(Year(rTo), Month(rTo), Day(rTo)))
                    'I use "CLng(DateSerial" because otherwise VBA may get confused
                    ' if the user's Excel i set to a non US dateformat.
            End With
        Next vItem
    End If
End If

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
End With

End Sub

Note that I've assigned the names dvFrom and dvTo to your data validation dropdowns, and then referenced those names in the code. You'll have to do the same in your master sheet.

If you don't want to change your PivotTable layout, then a workaround is to add a TimeLine on the month field, and programmatically change that via the dropdowns. It will then update the PivotTable. Here's how that looks:

Note that the Month field doesn't look like it has a filter applied, but it does...as evidenced by the totals that display in the PivotTable.

Here's the modified code for the Timeline-driven version:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim vItem As Variant
Dim rFrom As Range
Dim rTo As Range
Dim lFrom As Long
Dim lTo As Long
Dim dte As Date

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With

Set rFrom = Range("dvFrom")
Set rTo = Range("dvTo")

If Not Intersect(Target, rFrom) Is Nothing Or Not Intersect(Target, rTo) Is Nothing Then
    lFrom = CLng(DateSerial(Year(rFrom), Month(rFrom), Day(rFrom)))
    lTo = CLng(DateSerial(Year(rTo), Month(rTo), Day(rTo)))
    If lFrom < lTo Then
        For Each vItem In Array("NativeTimeline_Month", "NativeTimeline_Month1") 'Adjust timeline names as neccessary
            ActiveWorkbook.SlicerCaches(vItem).TimelineState. _
                SetFilterDateRange lFrom, lTo
        Next vItem
    End If
End If


With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
End With


End Sub

And that leaves us with your original approach - iterating through the PivotItems. It turns out that this is not just horribly inefficient, but also requires you temporarily to change your number format of the Month field from a MMM-YY format to a DD-MM-YY format or similar, because otherwise VBA might misinterpret the Year as a day, and then use the current year as the year. So if your PivotItem is OCT-15 then VBA interprets that as 15 October 2016 (the current year as I type this) instead of 1 October 2015. Nasty.

So I'd advise steering clear of iteration, and either change your PivotTable format and use my first approach, or add a TimeLine and use my second approach.