Forum Discussion

Rajendran_J's avatar
Rajendran_J
Copper Contributor
Oct 21, 2023

Excel Pivot table must filter based on the date value in a cell

Dear Team,
I want the Pivot table to automatically filter Settled Date based on the Target Date.
I expect Settled Date to be greater than or equal to the Target date value in J2.
Expected result:

Thank you,
Rajendran
Attached you will find editable sample file link:
Filter Date with Specific value in Cell.xlsx

  • Rajendran_J 

    Right-click the sheet tab.

    Select 'View Code' from the context menu.

    Copy the following code into the worksheet module:

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Range("J2"), Target) Is Nothing Then
            Application.ScreenUpdating = False
            Application.EnableEvents = False
            With Me.PivotTables("PivotTable1").PivotFields("Settled Date")
                .ClearAllFilters
                If IsDate(Range("J2").Value) Then
                    .PivotFilters.Add2 Type:=xlAfterOrEqualTo, _
                        Value1:=Format(Range("J2").Value, "Short Date")
                End If
            End With
            Application.EnableEvents = True
            Application.ScreenUpdating = True
        End If
    End Sub

    Switch back to Excel.

    Save the workbook as a macro-enabled workbook (*.xlsm).

    Make sure that you allow macros when you open the workbook.

Resources