Cómo actualizar dinámicamente todas las fuentes de datos de las tablas dinámicas en Excel

En un artículo anterior, aprendimos cómo cambiar y actualizar dinámicamente tablas dinámicas individuales con fuentes de datos que se reducen o expanden.

En este artículo, aprenda cómo podemos hacer que todas las tablas dinámicas en un libro cambien automáticamente la fuente de datos. En otras palabras, en lugar de cambiar una tabla dinámica a la vez, intentaremos cambiar la fuente de datos de todas las tablas dinámicas en el libro de trabajo para incluir dinámicamente nuevas filas y columnas agregadas a las tablas de origen y reflejar el cambio en las tablas dinámicas al instante.

Escribir código en la hoja de datos fuente

Como queremos que esto sea completamente automático, usaremos módulos de hoja para escribir código en lugar de un módulo principal. Esto nos permitirá usar eventos de la hoja de trabajo.

Si los datos de origen y las tablas dinámicas están en hojas diferentes, escribiremos el código VBA para cambiar la fuente de datos de la tabla dinámica en el objeto de hoja que contiene los datos de origen ( no que contiene tablas dinámicas).

Presione CTRL + F11 para abrir el editor de VB. Ahora vaya al explorador de proyectos y busque la hoja que contiene los datos de origen. Haga doble clic en él.

Se abrirá una nueva área de codificación. Es posible que no vea ningún cambio, pero ahora tiene acceso a los eventos de la hoja de trabajo.

Haga clic en el menú desplegable de la izquierda y seleccione la hoja de trabajo. En el menú desplegable de la izquierda, seleccione desactivar. Verá un sub en blanco escrito en el nombre del área de código worksheet_deativate. Nuestro código para cambiar dinámicamente los datos de origen y actualizar la tabla dinámica irá en este bloque de código. Este código se ejecutará siempre que cambie de la hoja de datos a cualquier otra hoja. Puede leer acerca de todos los eventos de la hoja de trabajo aquí.

Ahora estamos listos para implementar el código.

Código fuente para actualizar dinámicamente todas las tablas dinámicas en el libro de trabajo con un nuevo rango

Para explicar cómo funciona, tengo un libro de trabajo. Este libro de trabajo contiene tres hojas. Sheet1 contiene los datos de origen que pueden cambiar. Sheet2 y Sheet3 contienen tablas dinámicas que dependen de los datos de origen de sheet2.

Ahora he escrito este código en el área de codificación de sheet1. Estoy usando el evento Worksheet_Deactivate, por lo que este código se ejecuta para actualizar el tabla dinámica cada vez que cambiamos de la hoja de datos de origen.

Si tiene un libro de trabajo similar, puede copiar directamente estos datos. He explicado que este código funciona a continuación para que pueda modificarlo según sus necesidades.

Puede ver el efecto de este código en el gif a continuación.

¿Cómo cambia este código automáticamente? ¿Datos de origen y actualizar tablas dinámicas?

En primer lugar, usamos un evento worksheet_deactivate. Este evento se activa solo cuando la hoja que contiene el código se cambia o se desactiva. Así es como el código se ejecuta automáticamente.

Para obtener dinámicamente toda la tabla como rango de datos, determinamos la última fila y la última columna.

lstrow = Cells (Rows.Count, 1) .End (xlUp) .Row

lstcol = Cells (1, Columns.Count) .End (xlToL eft) .Column

Usando estos dos números definimos source_data. Estamos seguros de que el rango de datos de origen siempre comenzará desde A1. Puede definir su propia referencia de celda inicial.

Establecer source_data = Range (Cells (1, 1), Cells (lstrow, lstcol))

Ahora tenemos los datos de origen que son dinámica. Solo necesitamos usarlo en la tabla dinámica.

Como no sabemos cuántas tablas dinámicas contendrá un libro a la vez, recorreremos cada hoja y las tablas dinámicas de cada hoja. que no queda ninguna tabla dinámica. Para esto usamos bucles for anidados.

For Each ws In ThisWorkbook.Worksheets

For Each pt In ws.PivotTables

pt.ChangePivotCache _

ThisWorkbook.PivotCaches.Create (_

SourceType: = xlDatabase, _

SourceData: = source_data)

Siguiente pt

Siguiente ws

El primer ciclo recorre cada hoja. El segundo ciclo itera sobre cada tabla dinámica en una hoja.

Las tablas dinámicas están asignadas a la variable pt. Usamos el método ChangePivotCache del objeto pt. Creamos dinámicamente una caché dinámica usando ThisWorkbook.PivotCaches.Create

Método. Este método toma dos variables SourceType y SourceData. Como tipo de fuente declaramos xlDatabase y como SourceData pasamos el rango source_data que hemos calculado anteriormente.

Y que yo sentarse. Tenemos nuestras tablas dinámicas automatizadas. Esto actualizará automáticamente todas las tablas dinámicas en el libro de trabajo.

Así que sí, chicos, así es como pueden cambiar dinámicamente los rangos de fuentes de datos de todas las tablas dinámicas en un libro de trabajo en Excel. Espero haber sido lo suficientemente explicativo. Si tiene alguna pregunta sobre este artículo, hágamelo saber en la sección de comentarios a continuación.

Cómo actualizar dinámicamente el rango de origen de datos de la tabla dinámica en Excel: Para cambiar dinámicamente el rango de datos de origen de las tablas dinámicas, utilizamos cachés dinámicos. Estas pocas líneas pueden actualizar dinámicamente cualquier tabla dinámica cambiando el rango de datos de origen.

Cómo actualizar automáticamente las tablas dinámicas usando VBA: Para actualizar automáticamente sus tablas dinámicas, puede usar eventos de VBA. Utilice esta sencilla línea de código para actualizar su tabla dinámica automáticamente. Puede utilizar cualquiera de los 3 métodos de actualización automática de tablas dinámicas.

Ejecute la macro si se realiza algún cambio en la hoja en el rango especificado: en sus prácticas de VBA, tendría la necesidad de ejecutar macros cuando se haya determinado un rango o cambios celulares. En ese caso, para ejecutar macros cuando se realiza un cambio en un rango objetivo, usamos el evento de cambio.

Ejecutar macro cuando se realiza algún cambio en la hoja | Entonces, para ejecutar su macro cada vez que se actualiza la hoja, usamos los eventos de la hoja de trabajo de VBA.

El código VBA más simple para resaltar la fila y columna actual usando | Utilice este pequeño fragmento de VBA para resaltar la fila y columna actuales de la hoja.

Los eventos de la hoja de trabajo en Excel VBA | Los eventos de la hoja de trabajo son realmente útiles cuando desea que sus macros se ejecuten cuando ocurre un evento específico en la hoja.

Artículos populares:

50 accesos directos de Excel para aumentar su productividad | Acelera tu tarea. Estos 50 accesos directos le permitirán trabajar aún más rápido en Excel. La función VLOOKUP en Excel | Esta es una de las funciones más utilizadas y populares de Excel que se utiliza para buscar valores de diferentes rangos y hojas.

CONTAR.SI en Excel 2016 | Cuente valores con condiciones usando esta asombrosa función. No necesita filtrar sus datos para contar un valor específico. La función Countif es esencial para preparar su tablero.

Cómo usar la función SUMIF en Excel | Esta es otra función esencial del tablero. Esto le ayuda a resumir valores en condiciones específicas.