Dfyjkt

I have a sheet in Excel with a date column (dd/mm/yyyy)as follows:

A$Period Name
01/01/2018
01/02/2018
01/03/2018

When I look at the filter in the table I can see the dates as follows:

If I record a macro to filter this column I get the following code, which when I run it back it works correctly

ActiveSheet.Range("$A$1:$BP$5413").AutoFilter Field:=17, Operator:= _
 xlFilterValues, Criteria2:=Array(1, "10/1/2018")

The problem I have it in a pivot on the same data set. The date filter in the pivot looks like:

When I record the macro the code lookslike:

ActiveSheet.PivotTables("PivotTable41").PivotFields("A$Period Name"). _
 ClearAllFilters
ActiveSheet.PivotTables("PivotTable41").PivotFields("A$Period Name"). _
 CurrentPage = "10/1/2018" ' I tried with all dates. Showing Oct. here

When I try to run it back I get an error message in the last like like: Run-time error '1004': Application-defined or object-defined error.

I tried to change the date as dd/mm/yyyy (like the source) and dd-mmm-yy (like in the pivot) but it does not make a difference.

Any ideas on how to make it work? Also, I don't understand why when you record it the date it changes to mm/dd/yyyy.

I expect it'll work if you use the ISO date format:

ActiveSheet.PivotTables("PivotTable41").PivotFields("A$Period Name"). _
 CurrentPage = "2018-10-1"

In case it helps some one with a similar problem.

I managed to make it work doing the following:

DateVal = "01/10/2018"
DateVal = Format(DateVal, "d-mmm-yy")

ActiveSheet.PivotTables("PivotTable41").PivotFields("A$Period Name"). _
 ClearAllFilters
ActiveSheet.PivotTables("PivotTable41").PivotFields("A$Period Name"). _
 CurrentPage = DateVal