Problems to filter a pivot table with a date

[elvagonumero1]

Hello Guys!


I've been searching a lot looking for a solution to my problem and seems like this problem has been solved a lot out there, but those solutions doesn't apply to my case or i'm surely missing something.


I'm making a dashboard that refreshes everything on it, depending on a date value set on a cell via drop-down menu, after that I need that all pivot tables on the workbook be updated with the the date mentioned before, until now everything's alright but when I try to set the filter with the date value it give a 1004 error


I've tried three alternatives that are commented below


Look at it please

For Each Ws In ThisWorkbook.Sheets
        
            If Ws.Name = "MAT_DIA" Or Ws.Name = "CLIENTES_DIA" Or Ws.Name = "VENTAS_ZONA" Or _
                Ws.Name = "VEHÍ_DIA" Or Ws.Name = "ENT_DIA" Then
        
                Set Td = Ws.PivotTables(1)
                Set CampoTd = Td.PivotFields("FECHA DE LA VENTA (dd/mm/aaaa)")
                
                CampoTd.ClearAllFilters
                CampoTd.CurrentPage = Worksheets("DASHBOARD").Range("H6").value           
                'CampoTd.CurrentPage = DateSerial(Year(Worksheets("DASHBOARD").Range("H6").Value), _
                    Month(Worksheets("DASHBOARD").Range("H6").Value), Day(Worksheets("DASHBOARD").Range("H6").Value))
                'CampoTd.CurrentPage = CStr(Worksheets("DASHBOARD").Range("H6").Value)
                'CampoTd.CurrentPage =CLng(Worksheets("DASHBOARD").Range("H6").Value)
               
            End If
            
Next Ws

I hope someone can help me with this.


Thanks in advance

 

[elvagonumero1]

Hello!

I found something else and I'm think I'm getting closer.

I made a very similar routine before, I know that this is really basic, using instead of dates string values... I've change tha dates values for strings and it worked, I think that the problem could arise when I load the list of dates from the pivot table.

I'll show you

Sub Refresca_Dia_Dashboard()

    Dim Ws As Worksheet
    Dim Td As PivotTable
    Dim CampoTd As PivotField
    Dim Rg As Range
    Dim DirFechas As String
    
    Set Ws = Worksheets("DIAS")
    Set Td = Ws.PivotTables(1)
    
    Set CampoTd = Td.PivotFields("FECHA DE LA VENTA (dd/mm/aaaa)")
    
    Set Rg = CampoTd.DataRange
    
    DirFechas = Rg.Address

    With Worksheets("DASHBOARD").Range("H6").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=" & Ws.Name & "!" & DirFechas
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    
End Sub

I hope I can explain my problem clearly

What am I missing here?

Thanks in advance