Filter only results in the next 28 days from Pivot Table using VBA

[dannybland]

Hi,

I am looking to filter my table to show only the data from the next 28 days, I tried using the below but it returned an error. Included full example below

pt.PivotFields("Dates").PivotFilters.Add Type:=xlDateBetween, Value1:=Today(), Value2:=Today() + 28



Sub PTFour()


Sheets.Add
ActiveSheet.Name = "x"

Dim pt As PivotTable
Dim strField As String
Dim WSD As Worksheet
Set WSD = Worksheets("Raw Data")
Dim PTOutput As Worksheet
Set PTOutput = Worksheets("x")
Dim PTCache As PivotCache
Dim PRange As Range

' Find the last row with data
Dim finalRow As Long
finalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row

' Find the last column with data
Dim finalCol As Long
finalCol = WSD.Cells(1, Application.Columns.Count).End(xlToLeft).Column

' Find the range of the data
Set PRange = WSD.Cells(1, 1).Resize(finalRow, finalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)

' Create the pivot table
Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutput.Cells(1, 1), _
TableName:="EquityInvestmentPivot")

' Define the layout of the pivot table

' Set update to manual to avoid recomputation while laying out
pt.ManualUpdate = True

'Setting Fields
With pt

'set row field
With .PivotFields("Make/Model")
.Orientation = xlRowField
.Position = 1
End With


'set column field
With .PivotFields("Company Code")
.Orientation = xlColumnField
.Position = 1
End With


'set data field
.AddDataField .PivotFields("Remaining Equity Investment"), "Sum of Equity Invested", xlSum
End With

With ActiveSheet.PivotTables("EquityInvestmentPivot").PivotFields( _
"Sum of Equity Invested")
.NumberFormat = "£#,##0.00;[Red]-£#,##0.00"
End With

pt.PivotFields("Dates").PivotFilters.Add Type:=xlDateBetween, Value1:=Today(), Value2:=Today() + 28

' Now calc the pivot table
pt.ManualUpdate = False


ActiveSheet.PivotTables("EquityInvestmentPivot").TableStyle2 = _
"PivotStyleMedium4"
ActiveWorkbook.ShowPivotTableFieldList = False


MsgBox "Please see your requested pivot table. If you require another, please go back to the Pivot Table Selection tab."

End Sub

 

[Andrew Poulsom]

That's not what I had in my code.

PI.Visible = (DateValue(PI.Value) >= Date And DateValue(PI.Value) <= Date + 28)

 

[dannybland]

That didn't work either, I just tried the change to see if that worked.

 

[Andrew Poulsom]

Are you in the UK? The code worked for me when I tried it.

 

[dannybland]

ah - just realised, i have my dates in a different format - i'll reformat them and check

 

[dannybland]

No, still pulls all data.

    Dim Fmt As String    Dim PI As PivotItem
    With ActiveSheet.PivotTables("ExpiringEquityPivot").PivotFields("Termination Date")
        .ClearAllFilters
        Fmt = .NumberFormat
        .NumberFormat = "dd/mm/yyyy"
        For Each PI In .PivotItems
            Debug.Print CDate(PI.Value)
            PI.Visible = (DateValue(PI.Value) >= Date And DateValue(PI.Value) <= Date + 28)
        Next PI
        .NumberFormat = Fmt
    End With

Also, yes based in UK

 

[Andrew Poulsom]

What is your date format?

 

[dannybland]

dd/mm/yyyy

tried it a few times and also with the *dd/mm/yyyy format and no luck

 

[Andrew Poulsom]

That was the format my code uses. Please post some sample data for me to test.

 

[dannybland]

Make/Model	Termination Date	Company Code	Remaining Equity Investment	Funding Status	Detailed Funding Status
LEXUS IS	06/09/2015	COS	0	Fully Funded	Fully Funded (<12 Months)
AUDI A5	02/03/2014	COS	143.17	Part Funded	Part Funded (<12 Months)
AUDI A4	15/09/2013	CBF	5929.11	Part Funded	Part Funded (<12 Months)
VOLVO V60	17/03/2015	CBF	2219.87	Part Funded	Part Funded (<12 Months)
BMW 5	11/07/2015	CBL	0	Funder To Be Allocated	Funder To Be Allocated (<12 Months)
AUDI A5	10/11/2013	CBL	0	Fully Funded	Fully Funded (<12 Months)
VOLVO V40	01/02/2017	CBF	497.76	Part Funded	Part Funded (<36 Months)
BMW 5 SERIES	20/12/2015	CBF	6532.34	Part Funded	Part Funded (<24 Months)
AUDI A1	05/10/2014	COS	1383.51	Part Funded	Part Funded (<12 Months)
TOYOTA YARIS	31/01/2015	COS	0	Fully Funded	Fully Funded (<12 Months)
AUDI A3	17/01/2015	COS	1737.5	Part Funded	Part Funded (<12 Months)
AUDI A3	03/09/2014	COS	1870.2	Part Funded	Part Funded (<12 Months)
MERCEDES-BENZ C CLASS	03/01/2017	CBF	702.72	Part Funded	Part Funded (<36 Months)
AUDI Q3	12/12/2014	COS	0	Fully Funded	Fully Funded (<12 Months)
VOLKSWAGEN POLO	07/01/2015	COS	0	Fully Funded	Fully Funded (<12 Months)
VOLKSWAGEN POLO	28/01/2015	COS	0	Fully Funded	Fully Funded (<12 Months)
AUDI A3	11/03/2015	COS	0	Fully Funded	Fully Funded (<12 Months)
AUDI A1	17/09/2014	COS	0	Fully Funded	Fully Funded (<12 Months)
MERCEDES-BENZ E CLASS	01/09/2014	COS	187.69	Dealer Funded	Dealer Funded (<12 Months)
VOLKSWAGEN GOLF	23/12/2014	COS	0	Fully Funded	Fully Funded (<12 Months)
NISSAN NOTE	27/03/2015	COS	0	Fully Funded	Fully Funded (<12 Months)
AUDI Q3	28/09/2014	COS	22301.45	Self Funded (<12 Months)	Self Funded (<12 Months)
LEXUS RX	06/09/2015	COS	0	Fully Funded	Fully Funded (<12 Months)
LAND ROVER DISCOVERY	28/09/2014	COS	4669.88	Part Funded	Part Funded (<12 Months)
SEAT LEON	16/04/2015	COS	689.87	Part Funded	Part Funded (<12 Months)
AUDI A3	03/10/2014	COS	0	Fully Funded	Fully Funded (<12 Months)
VOLKSWAGEN PASSAT ALLTRACK	10/04/2015	COS	0	Fully Funded	Fully Funded (<12 Months)
TOYOTA YARIS	01/03/2015	COS	0	Fully Funded	Fully Funded (<12 Months)
VOLKSWAGEN GOLF	25/10/2014	COS	175.8	Part Funded	Part Funded (<12 Months)
NISSAN JUKE	10/04/2015	COS	0	Fully Funded	Fully Funded (<12 Months)
RENAULT CLIO	23/04/2015	COS	10418.93	Self Funded (<12 Months)	Self Funded (<12 Months)
NISSAN JUKE	30/04/2015	COS	0	Fully Funded	Fully Funded (<12 Months)
RENAULT CLIO	30/04/2015	COS	0	Fully Funded	Fully Funded (<12 Months)
TOYOTA AURIS	04/09/2015	COS	0	Fully Funded	Fully Funded (<12 Months)
TOYOTA YARIS	15/08/2015	COS	0	Fully Funded	Fully Funded (<12 Months)
NISSAN JUKE	01/09/2015	COS	0	Fully Funded	Fully Funded (<12 Months)
VOLKSWAGEN SCIROCCO	21/05/2015	COS	1926.4	Part Funded	Part Funded (<12 Months)
SEAT LEON	30/01/2015	COS	14859.85	Self Funded (<12 Months)	Self Funded (<12 Months)
TOYOTA AURIS	01/09/2015	COS	0	Fully Funded	Fully Funded (<12 Months)
SKODA CITIGO	11/07/2015	COS	1139.19	Part Funded	Part Funded (<12 Months)
SKODA OCTAVIA	28/05/2015	COS	17429.37	Self Funded (<12 Months)	Self Funded (<12 Months)
AUDI A3	30/05/2015	COS	666.95	Part Funded	Part Funded (<12 Months)
AUDI Q3	13/06/2015	COS	0	Fully Funded	Fully Funded (<12 Months)
AUDI A5	19/12/2014	COS	59.67	Part Funded	Part Funded (<12 Months)
VOLKSWAGEN UP	01/07/2015	COS	0	Fully Funded	Fully Funded (<12 Months)
AUDI A3	08/02/2015	COS	19960.28	Funder To Be Allocated	Funder To Be Allocated (<12 Months)
AUDI A1	01/08/2015	COS	0	Fully Funded	Fully Funded (<12 Months)
TOYOTA AURIS	01/09/2015	COS	0	Fully Funded	Fully Funded (<12 Months)
AUDI A3	01/03/2015	COS	29877.78	Funder To Be Allocated	Funder To Be Allocated (<12 Months)
AUDI A3	05/03/2015	COS	16533.2	Funder To Be Allocated	Funder To Be Allocated (<12 Months)
AUDI A3	01/03/2015	COS	19613.9	Funder To Be Allocated	Funder To Be Allocated (<12 Months)
NISSAN JUKE	17/09/2014	COS	0	Fully Funded	Fully Funded (<12 Months)
VAUXHALL INSIGNIA	19/11/2016	CBL	0	Fully Funded	Fully Funded (<36 Months)
VAUXHALL INSIGNIA	16/08/2014	CBL	0	Fully Funded	Fully Funded (<12 Months)
VAUXHALL INSIGNIA	21/06/2014	CBF	9231.74	Part Funded	Part Funded (<12 Months)
VOLKSWAGEN POLO	22/11/2014	COS	10150.23	Self Funded (<12 Months)	Self Funded (<12 Months)
TOYOTA AURIS	06/06/2015	COS	0	Fully Funded	Fully Funded (<12 Months)
AUDI A4	02/11/2014	COS	0	Fully Funded	Fully Funded (<12 Months)
LAND ROVER DISCOVERY	20/12/2014	COS	0	Dealer Funded	Dealer Funded (<12 Months)
VOLKSWAGEN GOLF	07/02/2015	COS	0	Fully Funded	Fully Funded (<12 Months)
AUDI Q5	23/12/2014	COS	76.16	Part Funded	Part Funded (<12 Months)
NISSAN JUKE	30/05/2015	COS	0	Fully Funded	Fully Funded (<12 Months)
AUDI A1	01/09/2015	COS	16561.13	Funder To Be Allocated	Funder To Be Allocated (<12 Months)
AUDI A6	03/03/2015	COS	42136.85	Funder To Be Allocated	Funder To Be Allocated (<12 Months)
AUDI Q5	01/02/2015	COS	0	Fully Funded	Fully Funded (<12 Months)
AUDI Q5	05/03/2015	COS	34966	Funder To Be Allocated	Funder To Be Allocated (<12 Months)
VOLKSWAGEN SCIROCCO	14/03/2015	COS	0	Fully Funded	Fully Funded (<12 Months)
VOLKSWAGEN UP	21/11/2014	COS	1455.49	Part Funded	Part Funded (<12 Months)
AUDI A5	01/04/2015	CBF	21170.44	Self Funded (<12 Months)	Self Funded (<12 Months)
AUDI A3	27/12/2014	COS	316.57	Part Funded	Part Funded (<12 Months)
TOYOTA AURIS	15/07/2015	COS	0	Fully Funded	Fully Funded (<12 Months)
AUDI A6	30/12/2014	COS	0	Dealer Funded	Dealer Funded (<12 Months)
AUDI A1	01/09/2015	COS	0	Fully Funded	Fully Funded (<12 Months)
AUDI A6	11/01/2015	COS	29184.8	Funder To Be Allocated	Funder To Be Allocated (<12 Months)
AUDI TT	28/07/2014	COS	0	Fully Funded	Fully Funded (<12 Months)
LAND ROVER RANGE ROVER	10/10/2014	COS	0	Fully Funded	Fully Funded (<12 Months)
BMW 1 SERIES	07/06/2016	CBF	1047.36	Part Funded	Part Funded (<24 Months)
NISSAN MICRA	24/10/2014	COS	8091.66	Self Funded (<12 Months)	Self Funded (<12 Months)
LAND ROVER RANGE ROVER SPORT	02/07/2014	COS	93.42	Part Funded	Part Funded (<12 Months)
AUDI A1	17/10/2014	COS	0	Fully Funded	Fully Funded (<12 Months)
SEAT LEON	04/11/2014	COS	2067.02	Part Funded	Part Funded (<12 Months)
LAND ROVER DISCOVERY	13/04/2016	CBL	0	Fully Funded	Fully Funded (<24 Months)
SEAT IBIZA	30/09/2014	COS	0	Fully Funded	Fully Funded (<12 Months)
SKODA SUPERB	16/03/2015	CBL	20874.99	Funder To Be Allocated	Funder To Be Allocated (<12 Months)
VOLVO V50	23/05/2016	CBF	1498.52	Part Funded	Part Funded (<24 Months)
VOLKSWAGEN POLO	16/10/2014	COS	0	Fully Funded	Fully Funded (<12 Months)
AUDI A3	13/06/2016	CBF	12300	Self Funded (<24 Months)	Self Funded (<24 Months)
NISSAN NOTE	27/03/2015	COS	0	Fully Funded	Fully Funded (<12 Months)
NISSAN NOTE	19/12/2014	COS	885.03	Part Funded	Part Funded (<12 Months)
MERCEDES-BENZ E CLASS	01/10/2014	COS	0	Fully Funded	Fully Funded (<12 Months)
SKODA OCTAVIA	28/04/2015	COS	19130.5	Self Funded (<12 Months)	Self Funded (<12 Months)
KIA SORENTO	26/08/2018	CBF	26153.57	Funder To Be Allocated	Funder To Be Allocated (<48 Months)
VOLKSWAGEN GOLF	24/09/2017	CBF	20618.31	Self Funded (<48 Months)	Self Funded (<48 Months)
NISSAN JUKE	24/09/2014	COS	0	Fully Funded	Fully Funded (<12 Months)
VOLKSWAGEN POLO	09/12/2014	COS	1054.65	Part Funded	Part Funded (<12 Months)
MERCEDES-BENZ C	26/01/2013	CBL	1	Funder To Be Allocated	Funder To Be Allocated (<12 Months)
AUDI A7	01/06/2016	CBF	7229.96	Part Funded	Part Funded (<24 Months)
HONDA CIVIC	01/06/2016	CBF	1049.28	Part Funded	Part Funded (<24 Months)
LAND ROVER RANGE ROVER EVOQUE	25/09/2016	CBF	20783.83	Self Funded (<36 Months)	Self Funded (<36 Months)
FORD MONDEO	09/07/2016	CBL	0	Fully Funded	Fully Funded (<24 Months)

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Andrew Poulsom]

If I run my VBA code on a pivot table created from that data I get:

Excel 2010
	H	I	J
1	Termination Date	(Multiple Items)
2
3	Sum of Remaining Equity Investment	Column Labels
4	Row Labels	COS	Grand Total
5	AUDI A1	1383.51	1383.51
6	AUDI A3	0	0
7	AUDI Q3	22301.45	22301.45
8	LAND ROVER DISCOVERY	4669.88	4669.88
9	LAND ROVER RANGE ROVER	0	0
10	MERCEDES-BENZ E CLASS	0	0
11	NISSAN JUKE	0	0
12	SEAT IBIZA	0	0
13	Grand Total	28354.84	28354.84
Sheet1