Macro VBA para actualizar todas las Tablas dinámicas de un archivo en Excel

En este artículo les comparto el desarrollo de un Formulario VBA que realiza la actualización (Clic derecho Actualizar) de todas la Tablas dinámicas contenidas en un archivo de Excel. Además, el formulario tendrá la capacidad de poder asignar la propiedad “Actualizar al abrir el archivo” a las mismas Tablas dinámicas.

Consulta también: Hacer un rango dinámico para una Tabla dinámica en Excel.

Cómo lo hacemos sin macros

Actualizar Tabla dinámica. Al momento de cambiar nuestros datos, la actualización de una Tabla dinámica debe hacerse de manera manual, a menos que usemos una macro para actualizar automáticamente al momento de activar una hoja. Para actualizar la Tabla dinámica deberemos de dar clic derecho y elegir la opción Actualizar.

Figura 1. Actualizar Tabla dinámica.

Actualizar al abrir el archivo. Al marcar esta opción cada que se abra el archivo, se actualizarán las Tablas dinámicas que tengan la opción marcada, la cual la activaremos dando clic derecho y elegir la opción Opciones de tabla dinámica. Dentro de la pestaña Datos marcaremos la opción Actualizar al abrir el archivo.

Figura 2. Actualizar tabla dinámica al archivo el archivo.

Video tutorial

Actualizar Tablas dinámicas con macros

En archivo de ejemplo tenemos 4 tablas dinámicas, las cuales deseamos que se actualicen todas a la vez al momento de presionar el botón Aceptar de un formulario.

Figura 3. Tablas dinámicas en Excel.

Cómo usar el formulario

El formulario tiene dos utilidades. Primero va a permitir que, al presionar Aceptar, se actualicen todas las Tablas dinámicas del archivo, evitando así el tener que hacerlo una por una. Además cuenta con una opción que nos permitirá que las todas las tablas dinámicas, se actualicen automáticamente al abrir el archivo.

Figura 4. Macro para actualizar tablas dinámicas en Excel.

Macro vba del formulario

Lo que tenemos que considerar de la macro es que usamos las colecciones de Sheets (Hojas) y PivotTables (Tablas dinámicas) para recorrer una por una y hacer la actualización.

'---------------------------------------------------------------------------------------
' Module    : UserForm1
' Author    : MVP Excel, Sergio Alejandro Campos
' Date      : 12/04/2016
' Website   : http://www.exceleinfo.com
' Youtube   : https://www.youtube.com/user/sergioacamposh
'---------------------------------------------------------------------------------------
'
Option Explicit
'
Private Sub btnAceptar_Click()
'Declaramos variables
    Dim Hoja As Worksheet
    Dim TD As PivotTable
    '
    'Recorremos cada hojas del archivo
    For Each Hoja In ActiveWorkbook.Sheets
        'Recorremos cada TD de cada hoja
        For Each TD In Hoja.PivotTables
            'Actualizar cada TD
            TD.RefreshTable
            'En caso de esta marcado el chkActualizarAlAbrir
            If Me.chkActualizarAlAbrir.Value = True Then
                TD.PivotCache.RefreshOnFileOpen = True
            Else
            End If
        Next TD
    Next Hoja
    '
    Unload Me
End Sub
'
Private Sub btnCancelar_Click()
    Unload Me
End Sub
'
Private Sub UserForm_Initialize()
'Declaramos variables
    Dim CuentaTD As Integer
    Dim TextoLabel As String
    Dim Hoja As Worksheet
    Dim TD As PivotTable
    '
    CuentaTD = 0
    '
    'Recorremos cada hojas del archivo
    For Each Hoja In ActiveWorkbook.Sheets
        'Recorremos cada TD de cada hoja
        For Each TD In Hoja.PivotTables
            CuentaTD = CuentaTD + 1
        Next TD
    Next Hoja
    '
    'Asignamos la cantidad de TD's que hay en el archivo.
    TextoLabel = "Actualizar " & CuentaTD & " Tablas dinámicas en el libro activo?"
    Me.Label1.Caption = TextoLabel
    '
End Sub

Anexos

Descargar el ejemplo usado en este artículo: Actualizar todas las TDs de un archivo.xlsm.