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

Carin

Board Regular
Joined
Feb 4, 2006
Messages
207
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
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Dtex20

Board Regular
Joined
Jan 29, 2018
Messages
50
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,
 

Carin

Board Regular
Joined
Feb 4, 2006
Messages
207
Dtex20 - When I copied and pasted your code, the first line is red and generates a Compile error: Syntax error
 

Carin

Board Regular
Joined
Feb 4, 2006
Messages
207
I still need help if anyone can help out. Thanks
 

Carin

Board Regular
Joined
Feb 4, 2006
Messages
207
I would love some help on this if anyone knows how. Thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,090,497
Messages
5,414,894
Members
403,552
Latest member
Daniel Kuenstler

This Week's Hot Topics

Top