To filter a PivotTable by a date range using VBA, you need to work with the `PivotFields` collection of the PivotTable object and apply the `PivotFilters` or manipulate the `VisibleItems` property. Below is an example of how you can achieve this by applying a date filter to a PivotTable with a date field.

Steps to Filter a PivotTable by Date Range Using VBA

• Identify the PivotTable: Know the name of the worksheet and the PivotTable you are working with.
• Determine the Date Field: Identify which field in your PivotTable contains the date data you wish to filter.
• Specify the Date Range: Define the start and end dates for your filter.

VBA Code Example

Here’s a VBA example that demonstrates how to filter a PivotTable based on a date range:

Sub FilterPivotTableByDate()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim startDate As Date
    Dim endDate As Date

    ' Set worksheet and PivotTable
    Set ws = ThisWorkbook.Worksheets("Sheet1") ' Adjust to your sheet name
    Set pt = ws.PivotTables("PivotTable1")     ' Adjust to your PivotTable name

    ' Specify the date range
    startDate = DateValue("2023-01-01")  ' Adjust to your start date
    endDate = DateValue("2023-12-31")    ' Adjust to your end date

    ' Set the PivotField to the date field you wish to filter
    Set pf = pt.PivotFields("DateField") ' Adjust to your actual date field name

    ' Clear any existing filters
    pf.ClearAllFilters

    ' Apply the date range filter
    With pf
        .PivotFilters.Add Type:=xlDateBetween, Value1:=startDate, Value2:=endDate
    End With

End Sub

Explanation

• Date Range: Adjust the `startDate` and `endDate` variables to your desired date range.
• PivotField: Replace `”DateField”` with the actual name of your date field in the PivotTable.
• Worksheet and PivotTable: Replace `”Sheet1″` and `”PivotTable1″` with your actual worksheet and PivotTable names.
• Filtering: This example uses `xlDateBetween` for date range filtering. Other types like `xlDateLastMonth`, `xlDateLastQuarter`, etc., can also be used if needed.

Note

• Ensure that the date field in your PivotTable is formatted correctly as a date so that VBA recognizes it as such.
• If you need to filter multiple fields simultaneously or apply more complex criteria, additional logic may be necessary.

Follow us on Social Media

facebooklinkedin

Unlock Your Potential

Excel

Access

Power BI

Help us grow the project