dannybland
New Member
- Joined
- Sep 12, 2014
- Messages
- 31
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
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
