Cómo actualización automática de tabla dinámica que utiliza VBA en Microsoft Excel
En este artículo, aprenderemos cómo actualizar automáticamente la tabla dinámica usando VBA en Microsoft Excel 2010.
¿Por qué necesitamos actualizar el gráfico de la tabla dinámica?
Como todos sabemos, cada vez que hacemos cambios en los datos de origen de una tabla dinámica, no se reflejan 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.
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.
Explicaré más adelante por qué usé 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. Por lo tanto, si tiene tablas dinámicas y datos en hojas diferentes, 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 utilizar 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.
Espero que este artículo sobre Cómo actualizar automáticamente la tabla dinámica usando VBA en Microsoft Excel sea explicativo. Encuentre más artículos sobre el cálculo de valores y fórmulas de Excel relacionadas aquí. Si te gustaron nuestros blogs, compártelo con tus amigos en Facebook. Y también puedes seguirnos en Twitter y Facebook. Nos encantaría saber de usted, háganos saber cómo podemos mejorar, complementar o innovar nuestro trabajo y hacerlo mejor para usted. Escríbanos a [email protected].
Artículos relacionados:
link: / excel-pivot-tables-how-to-dynamically-update-pivot-table-data-source-range-in-excel [Cómo actualizar dinámicamente el rango de fuente de datos de la tabla dinámica en Excel]: Para cambiar dinámicamente el rango de datos de origen de tablas dinámicas, utilizamos cachés dinámicos. 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, obtendrí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.
link: / tips-using-worksheet-change-event-to-run-macro-when-any-change-is-made [Ejecutar macro cuando se realiza algún cambio en la hoja]: Entonces, para ejecutar su macro siempre que la hoja actualizaciones, utilizamos los eventos de la hoja de trabajo de VBA.
link: / events-in-vba-simple-vba-code-to-highlight-current-row-and-column-using [Código VBA más simple para resaltar la fila y columna actual usando]: Use este pequeño fragmento de VBA para resaltar la fila y columna actual 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 sábana.
Artículos populares:
link: / tips-if-condition-in-excel [Cómo usar la función IF en Excel]: La declaración IF en Excel verifica la condición y devuelve un valor específico si la condición es VERDADERA o devuelve otro valor específico si es FALSO .
link: / formulas-and-functions-introduction-of-vlookup-function [Cómo usar la función VLOOKUP en Excel]: Esta es una de las funciones más utilizadas y populares de Excel que se usa para buscar valores de diferentes rangos y sábanas.
link: / excel-formula-and-function-excel-sumif-function [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.
link: / tips-countif-in-microsoft-excel [Cómo usar la función CONTAR.SI en Excel]: Cuente valores con condiciones usando esta función asombrosa. No necesita filtrar sus datos para contar valores específicos. La función Countif es esencial para preparar su tablero.