Keep getting an "Error 1004: item could not be found in the OLAP cube" when applying the filter to pivot table

[shallpass]

I have a pivot table where I select a month, but it's keep giving me an Error 1004: item could not be found in the OLAP cube.
I am pretty sure there is nothing wrong with my code because it was working fine from January to June. But, suddenly, starting from June, it stops working and give me that error. Does anyone know how to fix this issue? I've been trying to fix this issue for the past week, and I can't seem to find out what's wrong ( I think I saw somewhere that there is an issue with like a data model but not too sure.
Any help would be appreciated!!

Here's my code:

Sub Efficient()
'
' Efficient Macro
    Dim dates As Variant
    Dim i As Integer
   
    dates = Array("1/31/2019", "2/28/2019", "3/31/2019", "4/30/2019", "5/31/2019", "6/30/2019", "7/31/2019", "8/31/2019", "9/30/2019", "10/31/2019", "11/30/2019", "12/31/2019")
   
    For i = 0 To UBound(dates) Step 2
        ' Filter data in Solovis Book
        Windows("book to get all solovis data (2019) in one place.xlsx").Activate
        Sheets("Sheet 1 Copy and Paste").Activate
        ActiveSheet.Range("$A$1:$X$236096").AutoFilter Field:=18, Operator:=xlFilterValues, Criteria2:=Array(1, dates(i), 1, dates(i + 1))
        ActiveSheet.Cells.Copy
       
        ' Paste values in Final Portfolio Generator Workbook
        Windows("Final Portfolio Generator Workbook_Macro.xlsm").Activate
        Sheets("Sheet1 Copy&Paste").Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
       
        ' Refresh PivotTable and copy data to trial_2 sheet (First month)
        Application.Wait (Now + TimeValue("0:00:02"))
        Sheets("Breakdown").Activate
        ActiveWorkbook.RefreshAll
        Application.Wait (Now + TimeValue("0:00:03"))
       
        Dim ExactDate As String
        ExactDate = Format(dates(i), "yyyy-mm-dd") & "T00:00:00"
        ActiveSheet.PivotTables("PivotTable4").PivotFields("[Range].[Date].[Date]").ClearAllFilters
        ActiveSheet.PivotTables("PivotTable4").PivotFields("[Range].[Date].[Date]").VisibleItemsList = Array("[Range].[Date].[Date].&[" & ExactDate & "]")
       
        Application.Wait (Now + TimeValue("0:00:02"))
        Sheets("Centerbook for Backtesting(+%)").Select
        Range("I16").Select
        If Not Selection Is Nothing And Not IsEmpty(Selection) Then
            Range(Selection, Selection.End(xlDown)).EntireRow.Copy
            Sheets("trial_2").Select
            If IsEmpty(Range("A2").Value) Then
                Range("A2").Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
            Else
                Sheets("trial_2").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            End If
        End If
       
        ' Refresh PivotTable and copy data to trial_2 sheet (Second month)
        Sheets("Breakdown").Select
        ActiveSheet.PivotTables("PivotTable4").CubeFields(1).EnableMultiplePageItems = True
        ActiveSheet.PivotTables("PivotTable4").PivotFields("[Range].[Date].[Date]").VisibleItemsList = Array("[Range].[Date].&[" & Format(dates(i + 1), "yyyy-mm-dd") & "T00:00:00]")
       
        Sheets("Centerbook for Backtesting(+%)").Select
        Range("I16").Select
        If Not Selection Is Nothing And Not IsEmpty(Selection) Then
            Range(Selection, Selection.End(xlDown)).EntireRow.Copy
            Sheets("trial_2").Select
            If IsEmpty(Range("A2").Value) Then
                Range("A2").Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
            Else
                Sheets("trial_2").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            End If
        End If
       
    Next i

End Sub

 

[RoryA]

Which line gives the error when you debug, and what is the value of i at the time (assuming the code has got that far)?

 

[shallpass]

Which line gives the error when you debug, and what is the value of i at the time (assuming the code has got that far)?

Sorry, I should have mentioned it in the post. It was ActiveSheet.PivotTables("PivotTable4").PivotFields("[Range].[Date].[Date]").VisibleItemsList = Array("[Range].[Date].[Date].&[" & ExactDate & "]") that gave me an error.

And, to answer your second question, yesterday, my code was working fine until June, but as soon as 'i' goes to July, it gave me an error. But, when I ran it today, it didn't work at all. It stopped in January. So, this got me thinking that maybe code is not the problem, but the data model is the problem.

 

[RoryA]

Please use code tags when posting code - especially as yours includes fields in square brackets which the forum software tries to interpret as formatting tags otherwise.

Yes, it sounds like there is an issue with your data in that none of it appears to match the dates in question.

 

[shallpass]

Please use code tags when posting code - especially as yours includes fields in square brackets which the forum software tries to interpret as formatting tags otherwise.

Yes, it sounds like there is an issue with your data in that none of it appears to match the dates in question.

Will do. Do you have any ideas where I can start? This is my first time running into issues with data, so I'm kinda lost.

 

[RoryA]

You could use something like:

dim v
For each v in ActiveSheet.PivotTables("PivotTable4").PivotFields("[Range].[Date].[Date]").VisibleItemsList
debug.print v
next

to output a list of the items in the field to the immediate window and see if they match what you expect. I assume you've checked that the refresh operation you are doing beforehand has actually completed?