ryandonovan22
New Member
- Joined
- Sep 17, 2015
- Messages
- 36
I am doing a filter on Pivot data for filter on the last 12 months of data, because this does not seem to be built into excel.
This is my VBA code
The user inputs work fine, but the line "PivotFilters.Add2 Type:=xlDateBetween, Value1:=StartDate, Value2:=EndDate" does not seem to like either StartDate or EndDate being used.
Please help!
This is my VBA code
Excel Formula:
Sub Filterupdate()
'
' Filterupdate Macro
Dim StartDate As Date
Dim EndDate As Date
StartDate = InputBox("Choose Start date (dd/mm/yyyy)")
EndDate = InputBox("Choose End date (dd/mm/yyyy)")
Sheets("Rolling OTD").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Planned Delivery Date"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("Years").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("Quarters").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("Planned Delivery Date"). _
PivotFilters.Add2 Type:=xlDateBetween, Value1:=StartDate, Value2:=EndDate
End Sub
The user inputs work fine, but the line "PivotFilters.Add2 Type:=xlDateBetween, Value1:=StartDate, Value2:=EndDate" does not seem to like either StartDate or EndDate being used.
Please help!