Filtering Dates from Pivot Table

[XcelThug]

Hi Team,

• I have a PivotTable(PivotTable1) in sheet(Pivot).
• I need to filter out the field(Date) in this pivot using Range("F3") and Range("F4")

Attached is the file with data and below is the code that i have used and failed in getting the results.

Please help me out.
Please suggest any work around if the below code is completely wrong.
Note: The code which is there in the attached sheet is different from the below.

Sub Filter_ItemListInCode()
Dim pt As PivotTable
Dim Field As PivotField
Dim EDate As String
Dim SDate As String

Set pt = Worksheets("Pivot").PivotTables("PivotTable1")
Set Field = pt.PivotFields("Date")
SDate = Worksheets("Pivot").Range("F3").Value
EDate = Worksheets("Pivot").Range("F4").Value

With pt
Field.ClearAllFilters
Field.PivotFilters.Add Type:=xlDateBetween, Value1:=SDate, Value2:=EDate
pt.RefreshTable
End With

End Sub

 

[Monty]

XcelThug

If am working this file, i would definitely not go for VBA rather a simple way of getting things that is Slicers.

Let me know if this is okay else we can try with vba too.

 

[XcelThug]

XcelThug

If am working this file, i would definitely not go for VBA rather a simple way of getting things that is Slicers.

Let me know if this is okay else we can try with vba too.

Thank you @Monty , VBA would be good.
I am trying to Automate the file without using any slicers. So it would be nice if we can find a way through VBA itself. Please help.

 

[Chihiro]

Looking for something like this?

Sub Filter_ItemListInCode()
Dim pt As PivotTable
Dim Field As PivotField
Dim ptItem As PivotItem
Dim EDate As Date
Dim SDate As Date

Set pt = Worksheets("Pivot").PivotTables("PivotTable1")
pt.ManualUpdate = True
Set Field = pt.PivotFields("Date")
SDate = Worksheets("Sheet2").Range("A1").Value
EDate = Worksheets("Sheet2").Range("A6").Value

For Each ptItem In Field.PivotItems
    If ptItem.Value >= SDate And ptItem.Value <= EDate Then
        ptItem.Visible = True
    Else
        ptItem.Visible = False
    End If
Next
pt.ManualUpdate = False
End Sub

 

[Chihiro]

FYI - To use .PivotFilters.Add method, you need Date field in either Columns or Rows field.

 

[XcelThug]

@Chihiro Thanks a million. This worked great.