VBA change Pivot filter date

[JJCA99]

Hello,
I'm working in a Datavision Pivot table - not sure if that makes a difference.

I'm trying to change my pivot table date based on a cell value but I can't seem to find any code that works.
Keep getting a runtime error 1004
"unable to get the pivotfields property of the pivotTable Class"

Sub Macro1()
   Dim d As String
    d = "09/30/2017"   'Eventually this will equal cell "F2"

    Worksheets("DV").PivotTables("PivotTable1").PivotFields("date").PivotFilters.Add Type:=xlDateBetween, Value1:=d, Value2:=d
End Sub

Here is snapshot of my data: sorry not sure how to get this pasted in a better format

[TABLE="width: 513"]
<tbody>[TR]
[TD]POS 1 Tender
[/TD]
[TD](Multiple Items)
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10/1/2017
[/TD]
[/TR]
[TR]
[TD]POS 1 Category
[/TD]
[TD]Revenues
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]09/30/2017
[/TD]
[/TR]
[TR]
[TD]POS 1 Outlet
[/TD]
[TD]Banquets & Catering
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date
[/TD]
[TD]09/29/2017
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Check Audit Report
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Amount
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Level00
[/TD]
[TD]Level01
[/TD]
[TD]Total
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Date
[/TD]
[/TR]
</tbody>[/TABLE]

 

[SpillerBD]

I think you need to clear the previous filter first.
Test with suitable between dates or use a single criteria method,

 

[JJCA99]

I've tried that too but it didn't work. I tried to clear all filters and still the same issue.
-I also added d2 equal to a second date but I'm still getting the runtime error

Is it possible that the Pivotfields name, "date" is listed as something else - how would I check that?

 

[JJCA99]

One other thing I noticed, is when I use the filter search box and manually type in a date, I have to search by "DAY" .

There is a drop down list to the right of the search box (year, month, day) and I have to select day for the filter search to work.
-Is that causing my issue, and how would I fix that?

 

[SpillerBD]

Just record a macro setting the date item.
Field name should come from the header for that data field on the source. Preferably a Table.

 

[SpillerBD]

One other thing I noticed, is when I use the filter search box and manually type in a date, I have to search by "DAY" .

There is a drop down list to the right of the search box (year, month, day) and I have to select day for the filter search to work.
-Is that causing my issue, and how would I fix that?

umm yeah. Your field is a Date, but your original criteria is Text (String). You should use dates when you have dates...

 

[JJCA99]

ok so I tried recording and this is the code that came up - see below.
When I recorded the date as 9/30/2017 the code 6848 where d2 is for date.
-Now I'm getting error, "the item could not be found in the OLAP Cube"

   Dim d As String
    Dim d2 As String
    d = "09/29/2017"
    d2 = "6848"
    
    
        Range("B4").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("[Date].[Date]"). _
        AddPageItem "[Date].[Date].[Day].&[d2]", True
End Sub