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
 

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!
 

Forum statistics

Threads
1,084,744
Messages
5,379,588
Members
401,614
Latest member
priokatm

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top