Truco Excel. Actualizar los filtros de una tabla dinámica con Visual basic

24 Jul

Imaginad que tenéis que cambiar uno o varios filtros de todas las tablas dinámicas de una hoja y cada una de las tablas dinámicas tiene un nombre distinto o hay un número distinto de tablas dinámicas en cada hoja. Eso dificulta a la hora de crear un bucle para la modificación de filtros. Pues este truco de Excel os permitirá actualizar un filtro de una fecha (o cualquier otro) para todas las tablas dinámicas de una hoja. El código es muy sencillo y no creo necesario subiros a la web algún ejemplo:

Sub filtros()
‘Macro creada por www.analisisydecision.es
Dim pt As PivotTable

For Each pt In ActiveSheet.PivotTables

With pt.PivotFields(«fecha»)
.PivotItems(«Dec-10»).Visible = False
.PivotItems(«Mar-10»).Visible = True
End With
Next pt

End Sub

Para cada tabla dinámica (pivot table) actualiza el campo fecha, quita diciembre de 2010 y pone marzo de 2010. Es importante destacar que el filtro emplea fechas en lengua inglesa aunque vosotros en la tabla dinámica la veáis en lengua española. Esto es importante porque más de uno se ha vuelto loco con ese problema en las macros que modifican tablas dinámicas. También se puede plantear una versión que actualice todas las hojas de un libro. Espero que sea de utilidad. Saludos.

16 respuestas a «Truco Excel. Actualizar los filtros de una tabla dinámica con Visual basic»

  1. Hola,tengo como 100 tablas dinámicas que cambia cada semana y necesito cambiar el filtro según a la semana que corresponda. Por ejemplo, esta semana es la 44 y la próxima 45. Pues debo actualizar de la semana 44 a la 45 en todas las tabas, puedo hacerlo con macros?
    Gracias de antemano

    • Utiliza segmentación de datos y conectalo a todas las tablas dinámicas que tengas y listo con un click todo actualizado.

  2. Hola: quisiera saber como puedo actualizar mediante una macro los campos del area de valores de una tabla dinamica. Los datos provienen de una hoja en la que hay 12 columnas con numeros de cuenta y la idea es que el usuario pueda cambiarlos y mediante un boton ejecute la macro que crea la tabla dinamica.

    Tengo el siguiente codigo:

    ActiveSheet.PivotTables(«Tabla dinámica2»).ClearTable
    ActiveWorkbook.ShowPivotTableFieldList = True
    ActiveSheet.PivotTables(«Tabla dinámica2»).AddDataField ActiveSheet.PivotTables _
    («Tabla dinámica2»).PivotFields(«11100000»), «Cuenta de 11100000», xlCount
    With ActiveSheet.PivotTables(«Tabla dinámica2»).PivotFields( _
    «Cuenta de 11100000»)
    .Caption = «Suma de 11100000»
    .Function = xlSum
    End With

    Lo que varia es ese codigo 11000000 He intentado con variables pero no me funciona.

    Tambien he intentado solo actualizando pero igual no me funciona,

    ActiveSheet.PivotTables(«Tabla dinámica2»).PivotCache.Refresh
    ActiveWorkbook.RefreshAll

    With ActiveSheet.PivotTables(«Tabla dinámica2»).PivotFields&(«CTUNO»)
    .Orientation = xlRowField
    .Position = 6
    End With

    Agradezco su ayuda

  3. Pingback: Truco Excel. Actualizar el filtro de todas las tablas dinámicas de mi libro | Análisis y Decisión

  4. Hola!

    En este momento estoy en proceso de automatizar lo más posibles todos los procesos en mi área de trabajo.

    Uno de los proyectos que tengo es poder enviar estados de cuenta automáticamente por correo y el primer paso es actualizar el filtro de una tabla dinámica

    En contexto: Tengo una pivot filtrada por los clientes de la empresa. Mi idea es poder actualizar la pivot para cada cliente y con otro botón se envíe un correo a ese cliente con el texto predeterminado en una celda y pegando celdas de excel como imagen en dicho correo.

    Hay una manera de hacer todo eso en una sola macro o debería hacerlo por separado?

    Al momento tengo una macro para enviar correos, solo tendría que modificarla para pegar rangos como imagenes. Por el momento estoy buscando es la manera de cambiar al filtro de la pivot.

    Si tiene alguna sugerencia se lo agradeceré enormemente.
    Saludos

    • Hola, con esa descripción general es difícil ayudarte, aunque creo que sé lo que quieres hacer. Una pregunta: si ya tienes la macro que manda correos, ¿no puedes hacer un bucle para cada cliente? ¿O tienes que personalizar el correo?

  5. Hola, muy buen post me ayudo bastante pero me quede con una duda. si ya tengo la macro que me hace la tabla dinámica tal cual quiero pero quiero poner el diseño de informe en formato tabular, talvez podrías ayudarme con el código para eso?
    Saludos

  6. Me gustaría su ayuda para el siguiente problema, encontre el siguiente codigo para filtrar todas las tablas dinamicas de un libro en base a su filtro el problema es que solo funciona con una celda y lo que necesito es hacerlo con un rango de fecha. ejemplo en la A:2 fecha inicio
    B:2 fecha final

    y el filtro se actualice con ese rango, les agradezco toda la ayuda

    este es el código

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range(«F1»)) Is Nothing Then

    Dim hoja As Worksheet
    Dim td As PivotTable

    ‘Recorrer todas las hojas del libro
    For Each hoja In ThisWorkbook.Worksheets

    ‘Recorrer las tablas dinámicas de la hoja
    For Each td In hoja.PivotTables

    ‘En base al campo Region de la tabla dinámica:
    With td.PivotFields(«Region»)

    ‘Limpiar todos los filtros
    .ClearAllFilters

    ‘Filtrar por el valor de la celda F1
    On Error Resume Next
    .CurrentPage = Range(«F1»).Value

    End With

    Next td
    Next
    End If

    End Sub

  7. Buen dia, me podrian ayudar tengo el siguiente problema, tengo una tabla dinamica que me muestra informacion segun su filtro de fecha, tengo el siguiente codigo que me genero al grabar la macro…

    Sub Macro1()
    ActiveSheet.PivotTables(«Tabla dinámica3»).PivotFields(«Fecha Venta»). _
    CurrentPage = «(All)»
    With ActiveSheet.PivotTables(«Tabla dinámica3»).PivotFields(«Fecha Venta»)
    .PivotItems(«06/12/2016»).Visible = True
    End With
    End Sub

    «selecciono el filtro, me muestra todos mis pivotItems, que son puras fechas, y luego selecciono la fecha 06-12-2016»

    al ejecutar me sale error (1004 – «no se puede obtener la propiedad PivotItems de la clase PivotField») , ya intente metiendo otros formatos de fecha, no se que es lo que falla…

    La idea despues de solucionar este problema es meter la fecha con una variable

    Muchas Gracias, Ojala alguien pueda apoyarme!

  8. Hola!
    me gustaría saber como puedo indicar en una macro la fecha actual al momento de filtrar en una tabla dinámica.

    PivotTables(«Tabla dinámica1»).PivotFields(«FECHA»)
    .PivotItems(«fecha actual del sistema»).Visible = True

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *