Como las tablas de actualización automática de pivote con VBA Excel
Como todos sabemos, cada vez que hacemos cambios en los datos de origen de una tabla dinámica, no se refleja inmediatamente en la tabla dinámica. Necesitamos actualizar las tablas dinámicas para ver los cambios. Y si envía un archivo actualizado sin actualizar las tablas dinámicas, puede sentirse avergonzado. Entonces, en este artículo, aprenderemos cómo actualizar automáticamente una tabla dinámica usando VBA. De esta forma es más fácil de lo que imaginaba.
Esta es la sintaxis simple para actualizar automáticamente las tablas dinámicas en el libro de trabajo.
'Code in Source Data Sheet Object
Private Sub Worksheet_Deactivate()
sheetname_of_pivot_table.PivotTables("pivot_table_name").PivotCache.Refresh
End Sub
¿Qué son los cachés dinámicos? Cada tabla dinámica almacena los datos en la caché dinámica. Esta es la razón por la que pivot puede mostrar datos anteriores. Cuando actualizamos las tablas dinámicas, actualiza la caché con nuevos datos de origen para reflejar los cambios en la tabla dinámica.
Entonces solo necesitamos una macro para actualizar la caché de las tablas dinámicas. Haremos esto usando un link: / events-in-vba-the-worksheet-events-in-excel-vba [evento de la hoja de trabajo] para que no tengamos que ejecutar la macro manualmente. === ¿Dónde codificar para actualizar automáticamente las tablas dinámicas? Si sus datos de origen y tablas dinámicas están en hojas diferentes, entonces el código VBA debe ir en la hoja de datos de origen.
Aquí usaremos el evento Worksheet_SelectionChange. Esto hará que el código se ejecute cada vez que cambiemos de la hoja de datos de origen a otra hoja.
Más adelante explicaré por qué utilicé este evento.
Aquí, tengo datos de origen en sheet2 y tablas dinámicas en sheet1.
Abra VBE usando la tecla CTRL + F11. En el explorador de proyectos, puede ver tres objetos, Hoja1, Hoja2 y el Libro de trabajo.
Dado que Sheet2 contiene los datos de origen, haga doble clic en el objeto sheet2.
Ahora puede ver dos menús desplegables en la parte superior del área de código. Desde el primer menú desplegable, seleccione la hoja de trabajo. Y en el segundo menú desplegable, seleccione Desactivar. Esto insertará un subnombre vacío Worksheet_Deactivate. Nuestro código estará escrito en este sub. Cualquier línea escrita en este sub, se ejecuta tan pronto como el usuario cambia de esta hoja a cualquier otra hoja.
En sheet1 tengo dos tablas dinámicas. Quiero actualizar solo una tabla dinámica. Para eso, necesito saber el nombre de la tabla dinámica. Para conocer el nombre de cualquier tabla dinámica, seleccione cualquier celda en esa tabla dinámica y vaya a la pestaña de análisis de la tabla dinámica. En el lado izquierdo, verá el nombre de la tabla dinámica. También puede cambiar el nombre de la tabla dinámica aquí.
Ahora que conocemos el nombre de la tabla dinámica, podemos escribir una línea simple para actualizar la tabla dinámica.
Private Sub Worksheet_Deactivate()
Sheet1.PivotTables("PivotTable1").PivotCache.Refresh
End Sub
Y ya está.
Ahora, cada vez que cambie de los datos de origen, este código vba se ejecutará para actualizar la tabla dinámica1. Como puede ver en el gif a continuación.
¿Cómo actualizar todas las tablas dinámicas en el libro de trabajo? En el ejemplo anterior, solo queríamos actualizar una tabla dinámica específica. Pero si desea actualizar todas las tablas dinámicas en un libro de trabajo, solo necesita hacer pequeños cambios en su código.
Private Sub Worksheet_Deactivate()
'Sheet1.PivotTables("PivotTable1").PivotCache.Refresh
For Each pc In ThisWorkbook.PivotCaches
pc.Refresh
Next pc
End Sub
En este código estamos usando un link: / vba-for-loops-with-7-examples [For loop] para recorrer cada caché de pivote en el libro de trabajo. El objeto ThisWorkbook contiene todas las cachés dinámicas. Para acceder a ellos utilizamos ThisWorkbook.PivotCaches.
¿Por qué utilizar el evento Worksheet_Deactivate?
Si desea actualizar la tabla dinámica tan pronto como se realice algún cambio en los datos de origen, debe usar el evento Worksheet_Change. Pero no lo recomiendo. Hará que su libro de trabajo ejecute el código cada vez que realice algún cambio en la hoja. Puede que tenga que hacer cientos de cambios antes de que desee ver el resultado. Pero Excel actualizará la tabla dinámica con cada cambio. Esto dará lugar a una pérdida de tiempo y recursos de procesamiento. Entonces, si tiene tablas dinámicas y datos en diferentes hojas, es mejor usar Evento de desactivación de hoja de trabajo. Te permite finalizar tu trabajo. Una vez que cambia a las hojas de la tabla dinámica para ver los cambios, se corrigen los cambios.
Si tiene tablas dinámicas y datos de origen en la misma hoja y desea que las tablas dinámicas se actualicen automáticamente, puede usar `link: / tips-using-worksheet-change-event-to-run-macro-when- any-change-is-made [Worksheet_Change Event] `.
Private Sub Worksheet_Change(ByVal Target As Range)
Sheet1.PivotTables("PivotTable1").PivotCache.Refresh
End Sub
¿Cómo actualizar todo en los libros de trabajo cuando se realiza un cambio en los datos de origen? Si desea actualizar todo en un libro de trabajo (gráficos, tablas dinámicas, fórmulas, etc.) puede usar el comando ThisWorkbook.RefreshAll.
Private Sub Worksheet_Change(ByVal Target As Range) ThisWorkbook.RefreshAll End Sub
Tenga en cuenta que este código no cambia la fuente de datos. Entonces, si agrega datos debajo de los datos de origen, este código no incluirá esos datos automáticamente. Puede usar link: / table-excel-2007-17-amazing-features-of-excel-tables [Tablas de Excel] para almacenar datos de origen. Si no desea usar tablas, también podemos usar VBA para incluir nuevos datos. Lo aprenderemos en el próximo tutorial.
Así que sí amigo, así es como puede actualizar automáticamente las tablas dinámicas en Excel. Espero haber sido lo suficientemente explicativo y este artículo te haya servido bien. Si tiene alguna pregunta sobre este tema, puede preguntarme en la sección de comentarios a continuación.
Artículos relacionados:
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. En VBA use objetos de tablas dinámicas como se muestra a continuación …
link: / events-in-vba-run-macro-if-any-change-made-on-sheet-range [Ejecutar macro si se realiza algún cambio en la hoja en el rango especificado]: * En sus prácticas de VBA, lo haría obtienen la necesidad de ejecutar macros cuando un cierto rango o celda cambia. En ese caso, para ejecutar macros cuando se realiza un cambio en un rango objetivo, usamos el evento de cambio.
link: / tips-using-worksheet-change-event-to-run-macro-when-any-change-is-done [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.
link: / events-in-vba-simple-vba-code-to-resaltar-current-row-and-column-using [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.
link: / events-in-vba-the-worksheet-events-in-excel-vba [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.
Artículos populares:
link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-incrementa-your-productividad [50 accesos directos de Excel para aumentar su productividad] | Acelera tu tarea. Estos 50 atajos lo harán trabajar aún más rápido en Excel.
enlace: / fórmulas-y-funciones-introducción-de-función-vlookup [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. enlace: / tips-countif-in-microsoft-excel [COUNTIF 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.
link: / excel-formula-and-function-excel-sumif-function [Cómo utilizar la función SUMIF en Excel] | Esta es otra función esencial del tablero. Esto le ayuda a resumir valores en condiciones específicas.