VBA Refresh Pivot Table

Tabla dinámica de actualización de Excel VBA

Cuando insertamos una tabla dinámica en la hoja, una vez que los datos cambian, los datos de la tabla dinámica no cambian a sí mismos, debemos hacerlo manualmente, pero en VBA hay una declaración para actualizar la tabla dinámica que es expression.refreshtable , usando esto podemos actualizar el tabla dinámica haciendo referencia a la hoja de trabajo que la contiene o podemos hacer referencia a todas las tablas dinámicas en las hojas de trabajo y actualizarlas todas a la vez.

La tabla dinámica es vital para analizar la gran cantidad de datos. Ayuda desde analizar, resumir y hacer una interpretación de datos útil. Sin embargo, uno de los problemas con esta tabla dinámica es que no se actualizará automáticamente si hay algún cambio en los datos de origen, el usuario debe actualizar la tabla dinámica yendo a la tabla dinámica particular cada vez que haya un cambio. Pero despídase del proceso manual porque aquí tenemos el método para actualizar la tabla dinámica tan pronto como realice algún cambio en la tabla dinámica.

¿Cómo actualizar automáticamente el código VBA de datos de la tabla dinámica?

La única vez que se debe actualizar la tabla dinámica es cuando hay algún cambio en los datos de origen de la tabla dinámica a la que nos referimos.

Por ejemplo, observe los datos y la tabla dinámica a continuación.

Ahora cambiaré los números en los datos de origen, es decir, de A1 a B17.

En la celda B9 tengo que cambiar el valor de 499 a 1499, es decir, un aumento de 1000 en los datos, pero si miras el pivote todavía muestra el resultado como 4295 en lugar de 5295. Tengo que actualizar manualmente mi tabla dinámica para actualizar la tabla dinámica.

Para superar este problema, necesitamos escribir un código de macro de Excel simple para actualizar la tabla dinámica siempre que haya algún cambio en los datos de origen.

Puede descargar esta plantilla de Excel de tabla dinámica de actualización de VBA aquí - Plantilla de Excel de tabla dinámica de actualización de VBA

# 1 - Macro simple para actualizar toda la tabla

Paso 1: evento de cambio de la hoja de datos

Necesitamos activar el evento de cambio de la hoja de datos. En el editor visual básico, haga doble clic en la hoja de datos.

Una vez que haga doble clic en la hoja, seleccione "Hoja de trabajo" y seleccione el evento como "Cambiar".

Verá un subprocedimiento automático abierto como Worksheet_Change (ByVal Target As Range)

Paso 2: usar el objeto de hoja de trabajo

Consulte la hoja de datos mediante el objeto Worksheets.

Paso 3: consulte la tabla dinámica por nombre

Consulte el nombre de la tabla dinámica por el nombre de la tabla dinámica.

Paso 4: utilice el método Actualizar tabla

Seleccione el método como "Actualizar tabla".

Ahora, este código actualizará la tabla dinámica "PivotTable1" siempre que haya algún cambio en la hoja de datos de origen. Puede usar el siguiente código, solo tiene que cambiar el nombre de la tabla dinámica.

Código:

 Private Sub Worksheet_Change (ByVal Target As Range) Hoja de trabajo ("Hoja de datos"). PivotTables ("PivotTable1"). RefreshTable End Sub 

# 2 - Actualizar todas las tablas dinámicas de la misma hoja de trabajo

Si tiene muchas tablas dinámicas en la misma hoja de trabajo, puede actualizar todas las tablas dinámicas con un solo clic. Utilice el siguiente código para actualizar todas las tablas dinámicas de la hoja.

Código:

 Sub Refresh_Pivot_Tables_Example1 () Worksheets ("Hoja de datos"). Seleccione con ActiveSheet .PivotTables ("Table1"). RefreshTable .PivotTables ("Table2"). RefreshTable .PivotTables ("Table3"). RefreshTable .PivotTables ("Table4"). RefreshTable .PivotTables ("Table5"). RefreshTable End With End Sub 

Debe cambiar el nombre de la hoja de trabajo y los nombres de la tabla dinámica según los detalles de la hoja de trabajo.

# 3 - Actualizar todas las tablas en el libro de trabajo

Es muy poco probable que tengamos todas las tablas dinámicas en la misma hoja de trabajo. Por lo general, para cada informe, intentamos agregar tablas dinámicas separadas en hojas separadas. En estos casos, no podemos seguir escribiendo el código para cada tabla dinámica a actualizar.

Entonces, lo que podemos hacer es con un solo código usando bucles, podemos recorrer todas las tablas dinámicas en el libro de trabajo y actualizarlas con un solo clic del botón.

El siguiente código recorrerá cada tabla dinámica y las actualizará.

Codigo 1:

 Sub Refresh_Pivot_Tables_Example2 () Dim PT As PivotTable para cada PT en ActiveWorkbook.PivotTables PT.RefreshTable Siguiente PT End Sub 

Codigo 2:

 Sub Refresh_Pivot_Tables_Example3 () Atenuar PC como PivotCache para cada PC en ActiveWorkbook.PivotCaches PC.Refresh Next PT End Sub 

Ambos códigos harán la actualización de las tablas dinámicas.

Si desea que la tabla dinámica se actualice tan pronto como haya algún cambio en la hoja de datos de la hoja dinámica, debe copiar y pegar los códigos anteriores en el evento Cambio de hoja de trabajo en ese libro de trabajo.

# 4 - Evite el tiempo de carga usando el evento de desactivación de la hoja de trabajo

Cuando usamos el evento "Cambio de hoja de trabajo", se actualiza incluso cuando no hay cambios en la fuente de datos, pero si ocurre algún cambio en la hoja de trabajo.

Incluso si ingresa un solo punto en la hoja de trabajo, intenta actualizar la tabla dinámica. Entonces, para evitar esto, podemos usar el método "Desactivar hoja de trabajo" en lugar del método "Cambiar hoja de trabajo".

Desactive las actualizaciones de eventos en la tabla dinámica al pasar de una hoja a otra.

Original text