Filter a pivot based on date in a cell

[laxminarayana]

Hello,

My pivot table contains dates and product received. I am trying automate the filtering of date in pivot, so that when i enter a date in a desired cell, pivot would filter with the date in the cell and return the number. I have put up the following code to achieve this. But ended up with error as 'expected end of statement'. Please help me to fix the code.

Sub Macro1()Dim ws1 As Worksheet:   Set ws1 = Sheets("Sheet1")
ActiveSheet.PivotTables("PivotTable1").RefreshTable
ActiveSheet.PivotTables("PivotTable1").PivotFields("OrderDate").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields("OrderDate").PivotFilters. _
        Add Type:=xlSpecificDate, Value1:="ws1.Range("F1").Value"
End With
End Sub

Thanks

 

[sheetspread]

http://www.mrexcel.com/forum/excel-questions/520751-pivot-table-filter-cell-value.html

 

[laxminarayana]

Thank you.
I'm still unable to get this to work. As there seems to be difference in vba code when using 'Report Filter' and 'Row labels'.
I've placd my file in below link, as files cannot be attahed in post.
Inventory
Can you please have a look.
Thank you! for your time and information.

 

[sheetspread]

Try:

Sub filtbycellv()
Dim Pi As PivotItem
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")
.PivotItems(Range("c1").Value).Visible = True
For Each Pi In .PivotItems
If Pi.Value = Range("c1").Value Then
Pi.Visible = True
Else
Pi.Visible = False
End If
Next Pi
End With
ActiveSheet.PivotTables("PivotTable1").RefreshTable
End Sub

 

[laxminarayana]

Thank you very much.