How can I make my user input work with this Macro?

[ryandonovan22]

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

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!

 

[DanteAmor]

Try this

"PivotFilters.Add"

And the dates declared as string:

Sub Filterupdate()
'
' Filterupdate Macro

  Dim StartDate As String
  Dim EndDate As String
  
  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.Add Type:=xlDateBetween, Value1:=StartDate, Value2:=EndDate
End Sub

 

[ryandonovan22]

Try this

"PivotFilters.Add"

And the dates declared as string:

Sub Filterupdate()
'
' Filterupdate Macro

  Dim StartDate As String
  Dim EndDate As String
 
  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.Add Type:=xlDateBetween, Value1:=StartDate, Value2:=EndDate
End Sub

Thank you very much, this worked perfectly!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.