Problems filtering a Pivot Table with VBA

[Frank123]

I have have a data table something like this in worksheet "Base Ventas Asistido".

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

And in worksheet "Deporte" in cell C2 and C3 I have a START DATE and an END DATE.

With VBA I managed to creat this pivot table, that ended up somthing like this:

Now I want to filter between dates using this code:

Dim startDate As Date
Dim endDate As Date

If IsDate(ws_Dest.Range("C2").Value) Then
    startDate = ws_Dest.Range("C2").Text

Else
    MsgBox "The start date is not a valid date."
    Exit Sub
End If

If IsDate(ws_Dest.Range("C3").Value) Then
    endDate = ws_Dest.Range("C3").Text
Else
    MsgBox "The end date is not a valid date."
    Exit Sub
End If

    If IsDate(startDate) And IsDate(endDate) Then
        ' Apply the date filter
       With ptTable.PivotFields("Fecha Desde")
             .ClearAllFilters ' Clear any existing filter
             .PivotFilters.Add2 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

But I keep getting this error Error 1004 Application-defined or object-defined in this line:

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

Here is the whole code:

Sub CrearTablaDinamicaaaa()
    Dim ws_Source As Worksheet
    Dim ws_Dest As Worksheet
    Dim ptCache As PivotCache
    Dim ptTable As PivotTable
    Dim rangoDatos As Range
    Dim lastRow As Long
    Dim ptTableName As String
    
    
    
    Set ws_Source = ThisWorkbook.Sheets("Base Ventas Asistidos")
    Set ws_Dest = ThisWorkbook.Sheets("Deporte")
    ptTableName = "TablaDinamicaVentas"

    ' Define range where to place the Pivot Table
    lastRow = ws_Source.Cells(ws_Source.Rows.Count, "A").End(xlUp).Row
    Set rangoDatos = ws_Source.Range("A1:L" & lastRow)

    ' Crear un Pivot Cache
    Set ptCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rangoDatos)
    
    
    ' Crete Pivot Table
    Set ptTable = ptCache.CreatePivotTable(TableDestination:=ws_Dest.Range("H40"), TableName:=ptTableName)
    

    ' Configure Pivot Table
    With ptTable
        'Filter
        .PivotFields("División").Orientation = xlPageField
        .PivotFields("División").Position = 1

        'Filter
        .PivotFields("Fecha Desde").Orientation = xlPageField
        .PivotFields("Fecha Desde").Position = 2

        'Rows
        .PivotFields("SKU").Orientation = xlRowField
        .PivotFields("SKU").Position = 1

        'Create Values of "Ventas" (Sales)
        With .PivotFields("Ventas")
            .Orientation = xlDataField
            .Position = 1
            .Function = xlSum
            .NumberFormat = "#,##0"
            .Name = "Total Ventas"
        End With
    End With
    
    ' Refresh
ptTable.RefreshTable

'sort
With ptTable
    .PivotFields("SKU").AutoSort xlDescending, "Total Ventas"
End With

' Refresh
ptTable.RefreshTable

Dim startDate As Date
Dim endDate As Date

If IsDate(ws_Dest.Range("C2").Value) Then
    startDate = ws_Dest.Range("C2").Text
Else
    MsgBox "The start date is not a valid date."
    Exit Sub
End If


If IsDate(ws_Dest.Range("C3").Value) Then
    endDate = ws_Dest.Range("C3").Text
Else
    MsgBox "The end date is not a valid date."
    Exit Sub
End If

    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:=startDate, Value2:=endDate
             .PivotFilters.Add2 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

I allready saw this solution at DateBetween, change from a date to a value from a cell
But for some reason its not working for me.
¿Can any body help me please?

 

[LazyBug]

Hello! Try this:

Sub CrearTablaDinamicaaaa()
Dim ws_Source As Worksheet
Dim ws_Dest As Worksheet
Dim ptCache As PivotCache
Dim ptTable As PivotTable
Dim rangoDatos As Range
Dim lastRow As Long
Dim ptTableName As String
    
    Set ws_Source = ThisWorkbook.Sheets("Base Ventas Asistidos")
    Set ws_Dest = ThisWorkbook.Sheets("Deporte")
    ptTableName = "TablaDinamicaVentas"

    ' Define range where to place the Pivot Table
    lastRow = ws_Source.Cells(ws_Source.Rows.Count, "A").End(xlUp).Row
    Set rangoDatos = ws_Source.Range("A1:L" & lastRow)

    ' Crear un Pivot Cache
    Set ptCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rangoDatos)
    ' Crete Pivot Table
    Set ptTable = ptCache.CreatePivotTable(TableDestination:=ws_Dest.Range("H40"), TableName:=ptTableName)
    ' Configure Pivot Table
    With ptTable
        'Filter
        .PivotFields("División").Orientation = xlPageField
        .PivotFields("División").Position = 1

        'Filter
        .PivotFields("Fecha Desde").Orientation = xlPageField
        .PivotFields("Fecha Desde").Position = 2

        'Rows
        .PivotFields("SKU").Orientation = xlRowField
        .PivotFields("SKU").Position = 1

        'Create Values of "Ventas" (Sales)
        With .PivotFields("Ventas")
            .Orientation = xlDataField
            .Position = 1
            .Function = xlSum
            .NumberFormat = "#,##0"
            .Name = "Total Ventas"
        End With
    End With
    
    ' Refresh
    ptTable.RefreshTable
    
    'sort
    With ptTable
        .PivotFields("SKU").AutoSort xlDescending, "Total Ventas"
    End With
    
    ' Refresh
    ptTable.RefreshTable
    
    Dim startDate As Long, endDate As Long
    
    If IsDate(ws_Dest.Range("C2").Value) Then
        startDate = CLng(ws_Dest.Range("C2").Value)
        If IsDate(ws_Dest.Range("C3").Value) Then
            endDate = CLng(ws_Dest.Range("C3").Value)
            
            ' 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:=startDate, Value2:=endDate
                .PivotFilters.Add2 Type:=xlDateBetween, Value1:=startDate, Value2:=endDate
            End With
            
        Else
            MsgBox "not valid date"
            Exit Sub
    Else
        MsgBox "not valid date"
        Exit Sub
    End If
    
    ptTable.RefreshTable
        
End Sub

 

[Domenic]

To filter a PageField between two dates, I think you'll need to loop through each PivotField. Maybe something like this...

    If Not IsDate(ws_Dest.Range("C2").Value) Then
        MsgBox "The start date is not a valid date.", vbExclamation
        Exit Sub
    End If
   
    If Not IsDate(ws_Dest.Range("C3").Value) Then
        MsgBox "The end date is not a valid date.", vbExclamation
        Exit Sub
    End If
   
    Dim startDate As Date
    startDate = ws_Dest.Range("C2").Value
   
    Dim endDate As Date
    endDate = ws_Dest.Range("C3").Value
   
    Dim pvtItem As PivotItem
    Dim visibleItemsCount As Long
   
    With ptTable
        .ManualUpdate = True
        With .PivotCache
            .MissingItemsLimit = xlMissingItemsNone
            .Refresh
        End With
        With .PivotFields("Fecha Desde")
            .ClearAllFilters
            If .Orientation = xlPageField Then
                visibleItemsCount = .PivotItems.Count
                For Each pvtItem In .PivotItems
                    If CDate(pvtItem.Name) < startDate Or CDate(pvtItem.Name) > endDate Then
                        If visibleItemsCount > 1 Then
                            pvtItem.Visible = False
                            visibleItemsCount = visibleItemsCount - 1
                        Else
                            MsgBox "No items found.", vbExclamation '(optional)
                            .ClearAllFilters
                            Exit For
                        End If
                    End If
                Next pvtItem
            Else
                .PivotFilters.Add2 Type:=xlDateBetween, Value1:=startDate, Value2:=endDate
            End If
        End With
        .ManualUpdate = False
    End With

Hope this helps!