Auto Filter Pivot table

[Reaaz]

Good day

Can someone please assist me with a vba code that is able to update a pivot table based on a cell value. I am basically trying to get the pivot to filter by the date highlighted in red on the workbook. I have attached the work book, I would highly appreciate any assistance

Kind regards,
Reaaz Loonat

 

[albanvanrijsewijk]

Hi,

I don't get it, you want to replace the date filter already available in the pivot table filter by your own date filter?

Anyway, if you want to be sure to get an answer, I believe it's better to post your question in the VBA section.

Cheers.

 

[Reaaz]

Hi

Thank you for the response. What I was trying to get is for the date to filter based on the date in red which is a formula so each day new data will be added i would have liked the pivot table to automatically filter according to the formulated date.

 

[albanvanrijsewijk]

Hi,

I believe something like that would work but I think I don't have the right excel version to test it. I found it here: https://www.excelcampus.com/vba/filter-pivot-table-slicer-recent-date-period/

VBA code:

Sub Filter_PivotField()

'Description: Filter a pivot table or slicer for a specific date or period

'Source: excelcampus.com/vba/filter-pivot-table-slicer-recent-date-period



Dim sSheetName As String

Dim sPivotName As String

Dim sFieldName As String

Dim sFilterCrit As String

Dim pi As PivotItem



    'Set the variables

    sSheetName = "Sheet3"

    sPivotName = "PivotTable21"

    sFieldName = "Date"

    'sFilterCrit = "5/2/2016"

    sFilterCrit = ThisWorkbook.Worksheets("Sheet3").Range("I1").Value

 

    With ThisWorkbook.Worksheets(sSheetName).PivotTables(sPivotName).PivotFields(sFieldName)

        'Clear all filter of the pivotfield

        .ClearAllFilters

     

        'Loop through pivot items of the pivot field

        'Hide or filter out items that do not match the criteria

        For Each pi In .PivotItems

            If pi.Name <> sFilterCrit Then

                pi.Visible = False

            End If

        Next pi

     

    End With



End Sub