excel - valor - Filtro de tabla dinámica con vba

filtro multiple tabla dinamica vba

Question

Tengo un cuadro que recoge la información de una tabla dinámica que tengo con los datos diarios. Intento crear botones activeX para que pueda filtrar los datos que son como ROW LABEL, para ver cómo se comportaron mis datos en la última semana y en el último mes

Entonces, lo que tengo hasta ahora y no funciona es:

Private Sub weekbtn1_Click () Dim i As Integer

If weekbtn1 = True Then
i = 0
Do Until Datavalue(date) - i = 42005
With ActiveSheet.PivotTables("Pivotcompsprice").PivotFields("Date")
    .PivotItems DateValue(Date) - i.Visible = False
    i = i + 1
End With    
Loop

i = 0
Do Until i = 7
With ActiveSheet.PivotTables("Pivotcompsprice").PivotFields("Date")
    .PivotItems Datevalue(date) - i.Visible = True
End With
Loop
Else
End If

End Sub

Puse este 42005 porque es la última fecha en que tengo los datos, que es 1/1/2015 ... Estaba pensando que era posible filtrar todos los datos como "falso" y luego hacer True lo que quiero, pero no funciona!

¿Alguien me puede ayudar?

Answer 1

No es posible ocultar todos los elementos en un PivotField. Siempre tienes que dejar al menos uno visible.

Será mucho más rápido y más fácil si usa VBA para aprovechar la funcionalidad incorporada de los filtros de fecha , es decir, esto:

Aquí hay un archivo de muestra donde hago exactamente eso: https://1drv.ms/x/s!Ah_zTnaUo4DzjhezQ3OTq9tq1APC

Tenga en cuenta que esta funcionalidad solo está disponible en RowFields o ColumnFields. Por lo tanto, mi código a continuación no funcionará en PageFields.

Aquí hay una rutina genérica que le permite elegir el tipo de intervalo y el período de intervalo en los que desea filtrar el campo pivote, así como también seleccionar opcionalmente la fecha desde la que desea contar adelante / atrás.

Sub Pivots_FilterPeriod(sInterval As String, _
                        lNumber As Long, _
                        Optional vRelativeTo As Variant, _
                        Optional pf As PivotField)

    ''Programmer:        Jeff Weir
    ''Contact:           [email protected]
    ''Description:       Lets you programatically filter a Pivot RowField or ColumnField by specifying
    ''                   an interval type (e.g. days, weeks, months, quarters, years)
    ''                   as well as an interval count (e.g. 7, -7)
    ''                   If the optional vRelativeTo field is left blank, it counts back/foward from
    ''                   the youngest/oldest item depending on whether lNumber is positive/negative
    ''                   It leverages off the inbuilt DateFilters functionality, and as such does not
    ''                   work on RowFields.


    Dim dteDateAdd As Date
    Dim dteFrom As Date
    Dim dteTo As Date

    On Error GoTo errhandler

    If pf Is Nothing Then
        On Error Resume Next
        Set pf = ActiveCell.PivotField
        On Error GoTo errhandler
        If pf Is Nothing Then GoTo errhandler
    End If

    With pf
        If .DataType = xlDate _
            And .Orientation <> xlPageField _
            And .Orientation <> xlDataField Then

            If IsMissing(vRelativeTo) Or vRelativeTo = "" Then
                .AutoSort xlAscending, "Date"
                If lNumber > 0 Then
                    vRelativeTo = .PivotItems(1)
                Else
                    vRelativeTo = .PivotItems(.PivotItems.Count)
                End If
            End If

            Select Case UCase(sInterval)
                Case "D", "DD", "DDD", "DDDD", "DAY", "DAYS": sInterval = "d"
                Case "W", "WW", "WWW", "WWWW", "WEEK", "WEEKS": sInterval = "ww"
                Case "M", "MM", "MMM", "MMMM", "MONTH", "MONTHS": sInterval = "m"
                Case "Q", "QQ", "QQQ", "QQQQ", "QUARTER", "QUARTERS": sInterval = "q"
                Case "Y", "YY", "YYY", "YYYY", "YEAR", "YEARS": sInterval = "yyyy"
            End Select

            dteDateAdd = DateAdd(sInterval, lNumber, vRelativeTo)
            If lNumber > 0 Then
                dteDateAdd = dteDateAdd - 1
            Else
                dteDateAdd = dteDateAdd + 1
            End If

            If dteDateAdd < vRelativeTo Then
                dteFrom = dteDateAdd
                dteTo = vRelativeTo
            Else
                dteFrom = vRelativeTo
                dteTo = dteDateAdd
            End If

            With Application
                .ScreenUpdating = False
                .EnableEvents = False
                .Calculation = xlCalculationManual
            End With

            .ClearAllFilters
            .PivotFilters.Add2 _
                Type:=xlDateBetween, _
                Value1:=CStr(dteFrom), _
                Value2:=CStr(dteTo)
        End If
    End With

errhandler:
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With

End Sub

Aquí hay algunas capturas de pantalla que demuestran cómo se ve en la práctica, usando diferentes parámetros.

Esto muestra cómo filtrar en los últimos 5 días a partir de los datos más recientes:

Y al cambiar el signo, resulta que debemos querer los primeros 5 días a partir de los datos más antiguos registrados:

Si especifica una fecha real en ese campo RelativeTo, se contará hacia adelante / atrás a partir de ahí dependiendo de si el parámetro Number es positivo / negativo. Aquí están los próximos 5 días a partir de la fecha de hoy mientras escribo esto:

... y aquí están los últimos 5 días:

Le permitirá especificar si desea días, semanas, trimestres, meses o años. Por ejemplo, aquí están las últimas 2 semanas contando desde el registro más reciente:

Estoy usando un evento Worksheet_Change aquí para activarlo, pero puede conectarlo a un botón si lo desea y alimentarlo con los parámetros que desee.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim bContinue As Boolean
    If Not Intersect(Target, Range("Interval")) Is Nothing Then bContinue = True
    If Not Intersect(Target, Range("Number")) Is Nothing Then bContinue = True
    If Not Intersect(Target, Range("RelativeTo")) Is Nothing Then bContinue = True
    If bContinue Then Pivots_FilterPeriod [Interval], [Number], [RelativeTo], Sheet1.PivotTables(1).PivotFields("Date")

End Sub