Trying to filter pivot table gives application defined or object defined error

[nakedbamboo]

I am trying to refresh a pivot table after adding new monthly data to the source. The refresh works fine, but when I try to modify the dates shown with a filter it throws an Application-defined or object-defined error.

I fought it for a couple of days and then just decided to create the pivot table from scratch. When I create a new pivot table with all the data, and then I use the exact same line to do an xlDateBetween filter, it works fine. So I thought there must be something wrong with the original table. I deleted it and kept the table I just successfully created and filtered as the new table. I then went back to the effort of just refreshing it, but it has the same error on the filter line.

I have posted my code below. The commented lines are the ones that were used to create a new table and everything worked fine. I then commented them, used that same table with the same filter code, and got the error on the last line of code below. The refresh code worked, so I know it is pointing to the correct table.

Thanks for any help.

    monthVal = DateSerial(Year(Date), Month(Date) - 1, 1)
    monthVal2 = DateSerial(Year(Date), Month(Date) - 6, 1)


    wbAdjCDL.Activate
    RCnt1 = Cells(Rows.Count, 1).End(xlUp).Row
    Range(Cells(3, 1), Cells(RCnt1 - 1, 1)).Copy
    wbAnalCDL.Sheets("New CDL Data").Activate
    RCnt2 = Cells(Rows.Count, 1).End(xlUp).Row
    Cells(RCnt2 + 1, 2).PasteSpecial Paste:=xlValues
    wbAdjCDL.Activate
    Range(Cells(3, 2), Cells(RCnt1 - 1, 4)).Copy
    wbAnalCDL.Sheets("New CDL Data").Activate
    Cells(RCnt2 + 1, 5).PasteSpecial Paste:=xlValues
    RCnt3 = Cells(Rows.Count, 2).End(xlUp).Row
    
    Cells(RCnt2 + 1, 1) = monthVal
    Cells(RCnt2 + 1, 1).Copy
    Range(Cells(RCnt2 + 2, 1), Cells(RCnt3, 1)).PasteSpecial
    Cells(RCnt2, 1).Copy
    Range("A:A").PasteSpecial Paste:=xlPasteFormats
    Range(Cells(RCnt2, 3), Cells(RCnt3, 3)).FillDown
    Range(Cells(RCnt2, 4), Cells(RCnt3, 4)).FillDown


    RCnt = Cells(Rows.Count, 1).End(xlUp).Row
'    Sheets.Add.Name = "Pivot"
    
    Sheets("Pivot").Select


'    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="New CDL Data!R1C1:R" & RCnt & "6C7", Version:=xlPivotTableVersion15). _
'        CreatePivotTable TableDestination:="Pivot!R3C1", TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion15


    Set pvt = ActiveSheet.PivotTables("PivotTable2")


'    pvt.PivotFields("EOM Month").Orientation = xlColumnField
'    pvt.AddDataField ActiveSheet.PivotTables("PivotTable2").PivotFields("Total Posted Exp Adj Amt"), "Sum of Total Posted Exp Adj Amt", xlSum
'    pvt.PivotFields("Adj Type").Orientation = xlRowField
'    pvt.PivotFields("Adjustment Code").Orientation = xlRowField
'    pvt.PivotFields("Adj Code Description").Orientation = xlRowField
'    pvt.RowAxisLayout xlTabularRow
'    With pvt
'        For Each pvtFld In .PivotFields
'            pvtFld.Subtotals(1) = True
'            pvtFld.Subtotals(1) = False
'        Next pvtFld
'    End With
'    With pvt.PivotFields("EOM Month")
'        .PivotItems("(blank)").Visible = False
'    End With
'    pvt.PivotFields("EOM Month").AutoSort xlDescending, "EOM Month"


    pvt.PivotCache.Refresh
    pvt.PivotFields("EOM Month").PivotFilters.Add2 Type:=xlDateBetween, Value1:=monthVal2, Value2:=monthVal