using cell data to filter pivot table (date field)

[riverstar]

I am using a macro to update the a filter on a pivot table. The below script works great when the field is text however I can not get it to work with a date value.

I have tried several differnt changes but cannot get it to work. It will clear the filter but does not appear to recognise the date value from the cell as a valid selection. I have even tried to converting the date to a text value first with no luck.

My Pivot table (Campaigns) has two filters (Group and Date) which both need to be updated from different cells (A1 and B1). Below is the script that works and updates the Group filter.

Any help would be much appreciated. I am new to playing with VBA and seem to be completely lost with this one.


Sub Apply_Reporting_Group_Filter()
Dim pvtTable As PivotTable
Dim pvtField As PivotField
Dim pvtItem As PivotItem
Dim filterReportingGroup As String

Set pvtTable = Worksheets("Sheet1").PivotTables("Campaigns")
Set pvtField = pvtTable.PivotFields("Reporting Group")

filterReportingGroup = Worksheets("Data Control").Range("A1")

For Each pvtItem In pvtField.PivotItems
If pvtItem.Value = filterReportingGroup Then
pvtField.CurrentPage = filterReportingGroup
Exit For
End If
Next pvtItem
End Sub

 

[kcmuppet]

@AlphaFrog Many thanks for your reply.

I've been trying to get my head round how to adapt it to filter by a range of dates. For example if I had in Cell T3 01 Feb 2020 and in T4 30 Apr 2021.

I saw here XlPivotFilterType enumeration (Excel) that instead of xlSpecificDate, I could use xlBeforeOrEqualTo and xlAfterOrEqualTo, so tried to adapt your code like this:

Sub Filter_PT_Date_Range()
    Dim pt As PivotTable
    For Each pt In Sheet2.PivotTables
        With pt.PivotFields("Event Date")
            .ClearAllFilters
            .PivotFilters.Add Type:=xlAfterOrEqualTo, Value1:=Format(Range("T3").Value, "dd mmm yyyy")
            .PivotFilters.Add Type:=xlBeforeOrEqualTo, Value1:=Format(Range("T4").Value, "dd mmm yyyy")
        End With
    Next pt
End Sub

But when I run ran the code, nothing happened (none of the charts updated). I though perhaps the field name "Event Date" needed to be referenced differently, so I recorded a macro as below, to see how these fields would be referenced and then try to adapt it to the code. The recording gave me:

Sub Filter_PT_Date_Range()
' Macro1
    ActiveWorkbook.ShowPivotTableFieldList = True
    With ActiveSheet.PivotTables("PivotTable3").CubeFields( _
        "[q Events].[Event Date]")
        .Orientation = xlPageField
        .Position = 1
    End With
End Sub

...so I tried "[q Events].[Event Date]" instead of "Event Date" , but that didn't work, so I tried this:

Sub Filter_PT_Date_Range()
    Dim pt As PivotTable
    For Each pt In Sheet2.PivotTables
        With pt.CubeFields("[q Events].[Event Date]")
            .ClearAllFilters
            .PivotFilters.Add Type:=xlAfterOrEqualTo, Value1:=Format(Range("T3").Value, "dd mmm yyyy")
            .PivotFilters.Add Type:=xlBeforeOrEqualTo, Value1:=Format(Range("T4").Value, "dd mmm yyyy")
        End With
    Next pt
End Sub

... but that gives me a compile error at .ClearAllFilters.

Where am I going wrong?