Search code examples
excelvba

Pivot Table Filter By Date - Error 1004 Application-defined or object-defined error

Im trying to filter my PIVOT TABLE by Date but I keep getting same error: "Error 1004 Application-defined or object-defined error" and it points out at this part of the code:

.PivotFilters.Add Type:=xlDateBetween, Value1:=CLng(startDate), Value2:=CLng(endDate)

I put the IF statement to make sure that the date format is correct, but I keep getting the "Error 1004 Application-defined or object-defined error" (The format is okey)

This is what I have in C2 and C3

enter image description here

And this is what I have in the data with which I created the pivot table.

enter image description here

Picture of my pivot table:

enter image description here

Sub CreatePivotTable()

Some Code...


 ' Define start and end dates from the "Deporte" worksheet
    Dim startDate As Variant
    Dim endDate As Variant
    startDate = ws_Dest.Range("C2")
    endDate = ws_Dest.Range("C3")

    If IsDate(startDate) And IsDate(endDate) Then

        ' Apply the date filter
        With ptTable.PivotFields("Fecha Desde") ' Ensure this is the correct field name
            .ClearAllFilters ' Clear any existing filters
            .PivotFilters.Add Type:=xlDateBetween, Value1:=CLng(startDate), Value2:=CLng(endDate)
        End With

    Else
        MsgBox "The start date or end date is not a valid date."
    End If

    ' Refresh the PivotTable to apply the date filter
    ptTable.RefreshTable

End sub

Here is a table generated:

Fecha Desde SKU Ventas Division
29-01-2024 mpm23958 3000 Deporte
05-02-2024 mpm38754 2000 Regalos
19-02-2024 mpm48832 4000 Deporte
29-01-2024 mpm38784 300 Deporte
19-02-2024 mpm28494 500 Deporte
Solution
    • Looping through PivotItems to filter the expected date range
    Sub FilterPivotTable()
 ' Define start and end dates from the "Deporte" worksheet
    Dim startDate As Variant
    Dim endDate As Variant
    Dim pvtItem As PivotItem, iVal As Long
    Dim ws_Dest: Set ws_Dest = ActiveSheet ' for testing
    Dim ptTable: Set ptTable = ws_Dest.PivotTables(1) ' for testing
    startDate = ws_Dest.Range("C2")
    endDate = ws_Dest.Range("C3")
    If IsDate(startDate) And IsDate(endDate) Then
        startDate = CLng(startDate)
        endDate = CLng(endDate)
        ' Apply the date filter
        With ptTable.PivotFields("Fecha Desde") ' Ensure this is the correct field name
            For Each pvtItem In .PivotItems
                iVal = CLng(CDate(pvtItem.Value))
                pvtItem.Visible = (iVal >= startDate And iVal <= endDate)
            Next
        End With
    Else
        MsgBox "The start date or end date is not a valid date."
    End If
    ' Refresh the PivotTable to apply the date filter
    ptTable.RefreshTable
End Sub

    Question: For example: Start Date is 01/01/2024 and endDat is 24/03/2024. But its not filtering 08/01/2024. Why do you think this happens or what do you recomend I do?

    • Excel think 08/01/2024 is 2024 Aug 01 instead of 2024 Jan 08. Below code proves it.
    • If you have to use dd/MMM/yyyy on date column, you can use a UDF to convert a date string to its value.
    • Replace CLng(CDate(..)) and CLng(..) to Date2Lng(..) in FilterPivotTable().
    Function Date2Lng(ByVal sDate As String) As Long
    Dim aTxt
    aTxt = Split(sDate, "/")
    If UBound(aTxt) = 2 Then
        Date2Lng = CLng(VBA.DateSerial(aTxt(2), aTxt(1), aTxt(0)))
    Else
        Date2Lng = 0
    End If
End Function

Sub test()
    Dim sDay As String: sDay = "08/01/2024"
    Debug.Print Format(sDay, "yyyy MMM dd")
    Debug.Print CLng(CDate(sDay))
    Debug.Print Date2Lng(sDay)
    Debug.Print Format(Date2Lng(sDay), "yyyy MMM dd")
End Sub

    Output:

    2024 Aug 01
 45505 
 45299 
2024 Jan 08