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


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


This question generated 32 answers. To proceed to the answers, click here.

This thread is current as of September 15, 2014.


For more resources for Microsoft Excel