Conclusión: aprenda a usar una macro simple para actualizar las tablas dinámicas automáticamente cada vez que se realicen cambios en los datos de origen. También comparto una solución no macro para actualizar las tablas dinámicas cuando se abre el archivo. Incluye video tutorial y descarga de archivos de Excel.
Nivel de habilidad: Intermedio
Descargar el archivo de Excel
Si aprende mejor haciéndolo por su cuenta, puede descargue el archivo que estoy usando en el video para seguirlo. Aquí está el archivo de Excel que contiene el código VBA.
Refresh Pivot Table Automatically.xlsm (41,5 KB)
Actualizar tablas dinámicas automáticamente
¿Se pueden actualizar sus tablas dinámicas de forma inmediata y automática cuando cambian sus datos de origen?
Por supuesto. Requiere el uso de una macro realmente simple que le mostraré cómo crear a continuación.
Si no está demasiado familiarizado con las macros y VBA, sugiero que revise mi serie de videos gratuitos de 3 partes sobre cómo comenzar con Macros & VBA.
Además, si es nuevo en las tablas dinámicas, Tengo una serie para explicarle qué son y cómo utilizarlas. Vea el primer vídeo de esa serie sobre tablas dinámicas & Dashboards
Para actualizar automáticamente nuestras tablas dinámicas, vamos a escribir una macro con una simple instrucción. Esa instrucción básicamente dice: cuando hago un cambio en mi hoja de trabajo, actualizo todas las tablas dinámicas y conexiones de datos. Estos son los pasos para crear la macro.
Abra el Editor de Visual Basic.
Puede hacerlo haciendo clic en el botón Visual Basic en la pestaña Desarrollador de la cinta.
El atajo de teclado para abrir el editor de Visual Basic es Alt + F11.
Si no ve la pestaña Desarrollador, puede hacerla visible siguiendo las instrucciones aquí. Solo tiene que hacer esto una vez, y luego la pestaña Desarrollador siempre estará visible cada vez que abra Excel en el futuro.
Abra el Módulo de hoja que contiene sus datos de origen.
En la ventana Explorador de proyectos del editor de Visual Basic, busque el libro que desea cambiar. Debajo de ese libro de trabajo se enumeran las hojas dentro del libro de trabajo. Seleccione la hoja que contiene los datos de origen. Luego haga doble clic en él.
Si no ve la ventana del Explorador de proyectos, puede habilitarla desde la Vista menú (atajo de teclado: Ctrl + R).
Agregar un nuevo evento para cambios en la hoja de trabajo.
Al hacer doble clic en la hoja, se abre el módulo de código para ese objeto. Dentro del módulo de código, queremos crear una macro de eventos. Para hacerlo, elija Hoja de trabajo en el cuadro desplegable Objeto a la izquierda.
Eso agregará un evento Worksheet_SelectionChange a el módulo, que en realidad no queremos, así que lo eliminaremos en un momento. Antes de hacerlo, vayamos al menú desplegable Procedimiento a la derecha y seleccione Cambiar.
Esto agrega un nuevo evento en la parte superior llamado Worksheet_Change. Ahora resaltaremos y eliminaremos el código innecesario debajo de él.
La macro de evento Worksheet_Change se ejecutará cada vez que se realice un cambio a las celdas de esa hoja de trabajo. Podemos agregar código VBA al evento Worksheet_Change para realizar acciones cuando el usuario edita celdas.
Nota: El evento SelectionChange que se agrega de forma predeterminada se ejecutará cada vez que el usuario seleccione una celda en la hoja. Como solo queremos que el código se ejecute cuando el usuario edita / cambia celdas, usamos el evento Change. Consulte mi artículo sobre módulos de código VBA & Cómo ejecutar macros según eventos de usuario para obtener más información sobre los módulos y eventos de la hoja.
Agregue el código VBA para actualizar todas las tablas dinámicas.
A continuación, justo debajo de la línea Worksheet_Change, escriba esta instrucción:
ThisWorkbook.RefreshAll
El método RefreshAll actualizará todas las tablas dinámicas, consultas y conexiones de datos en el libro de trabajo. Esta acción es la misma que si hace clic manualmente en el botón Actualizar en la pestaña Datos.
Agregar esta línea de código al evento Worksheet_Change actualizará el libro de trabajo cada vez que se realice un cambio en la hoja de trabajo en la que el código es en.
Tabla dinámica & Datos de origen en la misma hoja
Aleksandrs hizo una gran pregunta sobre los comentarios del video de YouTube. Si la tabla dinámica y los datos de origen están en la misma hoja, deberá agregar código para deshabilitar los eventos.
La actualización coloca el evento en un ciclo recursivo y puede terminar bloqueando Excel. Aquí está el código para evitar eso.
Application.EnableEvents = False ThisWorkbook.RefreshAllApplication.EnableEvents = True
Verificación para asegurarse de que su macro se esté ejecutando
Una forma de verificar si la macro está funcionando es probarlo. Realice un cambio en los datos de origen y vea si se refleja en su tabla dinámica.Si su cambio no es fácil de detectar porque tiene demasiados datos o por alguna otra razón, hay otra forma de ver si su macro se está activando.
En el editor de VB, puede hacer clic en el columna gris justo a la izquierda de su macro Worksheet_Change. Esto hará que aparezca un círculo rojo. También resalta esa línea de código en rojo.
Esto se llama parada o punto de interrupción.
El atajo de teclado para activar / desactivar un punto de interrupción es: F9
Ahora, siempre que ocurra una acción que active la macro, Excel saltará al Editor de VB y pausará la macro para que pueda verificar el código. En nuestro caso, esa acción es cualquier cambio que se realice en la hoja de trabajo.
Luego puede presionar F8 para recorrer cada línea, o presione F5 para ejecutar hasta el final (o el próximo punto de interrupción).
Si realiza un cambio en la hoja de trabajo y Excel no lo lleva al Editor VB, sabe que hay un problema con la macro no se está ejecutando. Si este es el caso, es probable que no haya guardado el archivo como un libro de trabajo habilitado para macros (.xlsm) o macros habilitadas. Es posible que deba guardar & cerrar el archivo, volver a abrirlo y habilitar las macros.
Para eliminar el punto de interrupción que ha colocado en la macro, simplemente haga clic en el círculo rojo para que desaparezca (atajo de teclado: F9).
El atajo de teclado para borrar todos los puntos de interrupción es: Ctrl + Shift + F9
Actualizar tablas dinámicas sin un Macro
Una desventaja de usar esta macro para actualizar sus tablas dinámicas es que el historial de deshacer se pierde cada vez que se ejecuta la macro. En otras palabras, cuando hace clic en el botón Deshacer (o presiona Ctrl + Z), Excel no recuerda lo último que hizo, por lo que no puede deshacerlo. En consecuencia, no pasará nada y su último cambio no se deshará.
Existe una alternativa que le permite mantener su historial de Deshacer. Sin embargo, esta alternativa solo actualiza su tabla dinámica cuando se abre el libro de trabajo, no cada vez que se realiza un cambio. Así es como puede usar esa opción.
Comenzando desde cualquier celda en su tabla dinámica:
- Vaya a la pestaña Analizar en la cinta.
- Elija el botón Opciones.
- Vaya a la pestaña Datos en la nueva ventana que se abre.
- Marque la casilla que dice «Actualizar datos al abrir el archivo».
Después de hacer clic en Aceptar, es posible que obtenga el siguiente mensaje de advertencia si tiene varias tablas dinámicas creadas a partir del mismo rango de datos de origen. Simplemente haga clic en Aceptar para completarlo.
De nuevo, solo a modo de comparación, Si usa esta opción, conserva el historial de deshacer, pero solo actualiza la tabla dinámica cuando el libro de trabajo se cierra y se vuelve a abrir. Si usa la opción macro, pierde el historial de deshacer, pero la tabla dinámica se actualiza automáticamente tes cada vez que se realiza algún cambio en el libro de trabajo.
Variaciones para actualizar las tablas dinámicas
La macro que analizamos no solo actualizará sus tablas dinámicas, sino que también actualizará las consultas . Si desea actualizar solo las tablas dinámicas, puede reemplazar el comando «ThisWorkbook.RefreshAll» con este código en su lugar:
Sub Refresh_All_Pivot_Table_Caches()"Refresh all pivot caches in the workbook."Pivot tables are automatically refreshed when cache is refreshed.Dim pc As PivotCache "Refresh all pivot tables For Each pc In ThisWorkbook.PivotCaches pc.Refresh Next pc End Sub
Cada tabla dinámica está conectada a un caché dinámica subyacente, que está conectada a los datos de origen. El código recorre todas las cachés dinámicas del libro de trabajo y actualiza cada una. Las tablas dinámicas del mismo rango de origen pueden compartir cachés dinámicas, por lo que este método es más rápido que recorrer todas las tablas dinámicas .
De manera similar, digamos que solo desea actualizar una tabla dinámica en particular. En ese caso, puede intercambiar el código «ThisWorkbook.RefreshAll» por el código siguiente.
Y finalmente, si está utilizando Power Query y desea deshabilitar la actualización en segundo plano para que las consultas se actualicen ANTES de las tablas dinámicas, he escrito un artículo para explicar cómo hacerlo al deshabilitar la actualización en segundo plano en las consultas.
Use el Desactivar evento en su lugar
Otra opción es utilizar el evento Worksheet_Deactivate en lugar de Worksheet_Change. El evento Worksheet_Deactivate se ejecutará cada vez que el usuario abandone la hoja y seleccione una hoja diferente. Esto permite al usuario realizar todos los cambios en los datos de origen, la tabla dinámica se actualizará automáticamente cuando pasen a cualquier otra hoja, incluidas las hojas que contienen la tabla dinámica.
Private Sub Worksheet_Deactivate() ThisWorkbook.RefreshAllEnd Sub
Este código todavía se colocaría en el módulo de hoja que contiene los datos de origen. Esta es una buena opción si sus tablas dinámicas o conexiones de datos toman un unos segundos o más para actualizar, y no desea esperar cada vez que se realiza un cambio en los datos de origen.
El único momento en el que puede no querer usar esto es si su tabla dinámica y su fuente los datos están en la misma hoja.Por lo general, ese será un caso poco común y algo que generalmente no recomiendo.
Gracias a la sugerencia de Ted en este caso.
Ahorre tiempo & Vergüenza
Espero que este artículo le ayude a ahorrar tiempo y lo haga más fácil para los usuarios de sus archivos. También puede ayudar a evitar la vergüenza cuando se olvida de actualizar las tablas dinámicas antes de enviar informes. yo, he cometido este error más veces de las que me gustaría admitir … 🙂
