En un artículo anterior, aprendimos cómo puede cambiar y actualizar dinámicamente tablas dinámicas individuales con fuentes de datos reducidas o expandidas.
En este artículo, aprenderemos cómo podemos hacer que todas las tablas dinámicas de 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 de origen
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 el 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 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 la tabla dinámica cada vez que cambiamos de la hoja de datos de origen.
Private Sub Worksheet_Deactivate () Dim source_data As Range 'Determinando la última fila y el número de columna lstrow = Cells (Rows.Count, 1) .End (xlUp) .Row lstcol = Cells (1, Columns.Count) .End (xlToLeft) .Column 'Estableciendo el nuevo rango Establecer source_data = Range (Cells (1, 1), Cells (lstrow, lstcol))' Código para recorrer cada hoja y tabla dinámica Para cada ws en ThisWorkbook.Worksheets For each pt En ws.PivotTables pt. ChangePivotCache _ ThisWorkbook.PivotCaches.Create (_ SourceType: = xlDatabase, _ SourceData: = source_data) Next pt Next ws End Sub
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 gif a continuación.
¿Cómo cambia este código automáticamente los datos de origen y actualiza las 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 se ejecuta automáticamente el código.
Para obtener dinámicamente toda la tabla como rango de datos, determinamos la última fila y la última columna.
lstrow = Celdas (Rows.Count, 1) .End (xlUp) .Row
lstcol = Celdas (1, Columns.Count) .End (xlToLeft) .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ámicos. Solo necesitamos usarlo en la tabla dinámica.
Como no sabemos cuántas tablas dinámicas contendrá un libro de trabajo a la vez, recorreremos cada hoja y las tablas dinámicas de cada hoja. Para que no quede ninguna tabla dinámica. Para esto usamos bucles for anidados.
Para cada ws en este libro de trabajo.
Para cada punto en ws.Tablas dinámicas
pt.ChangePivotCache _
ThisWorkbook.PivotCaches.Create (_
SourceType: = xlDatabase, _
SourceData: = source_data)
Siguiente pt
Siguiente ws
El primer bucle recorre cada hoja. El segundo ciclo itera sobre cada tabla dinámica en una hoja.
Las tablas dinámicas se asignan a la variable pt. Usamos el método ChangePivotCache del objeto pt. Creamos dinámicamente un caché dinámico 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 eso es todo. 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 con respecto a 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, usamos 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 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.
Ejecutar 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 cambia un cierto rango o celda. 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 | El evento de la hoja de trabajo es realmente útil cuando desea que sus macros se ejecuten cuando ocurre un evento específico en la hoja.
Articulos 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 BUSCARV 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.