VBA to Filter Pivot Table based on Start/End Date in Cells

Carin

Board Regular
Joined
Feb 4, 2006
Messages
224
I have a pivot table named PivotTable2 on a Worksheet named Three Pivots. One of the FILTERS is named DATE_DUE. I need the filter to have only the last five days checked. Currently I have the start date in cell C5 and the end date in cell C1 (If I don't need the cell reference, that is okay) Today is 10/04/2019 so I would need any date between and including 09/29/2019 through 10/03/2019 checked.

Thank you
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Hi

I've got some code, used before to do a similar thing:

Code:
Private Sub Filter_Pivot(xP As Variant, xPField As String)    Dim ws As Worksheet
    Dim xPElement  As Variant
    Dim xPTable As PivotTable
    Dim xPFile As PivotField
    Dim startDate As Date
    Dim endDate As Date
    
    Set ws = ThisWorkbook.Worksheets("*")
    
    startDate = ws.Range("C1").Value
    endDate = ws.Range("C5").Value


    With Worksheets("*")
        For Each xPElement In xP
            Set xPTable = .PivotTables(xPElement)
            Set xPFile = xPTable.PivotFields(xPField)
            xPFile.ClearAllFilters
            xPFile.PivotFilters.Add Type:=xlDateBetween, Value1:=CLng(startDate), Value2:=CLng(endDate)
        Next xPElement
    End With
End Sub

I have my dates field in the 'Rows' field, collapsed under the other dimension i am filtering by.

Maybe someone else can help you adapt it to your project.

Thanks,
 
Upvote 0
Dtex20 - When I copied and pasted your code, the first line is red and generates a Compile error: Syntax error
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top