Tabla dinámica de VBA | Pasos para crear una tabla dinámica en VBA

Tabla dinámica de Excel VBA

Las tablas dinámicas son el corazón para resumir el informe de una gran cantidad de datos. También podemos automatizar el proceso de creación de una tabla dinámica mediante la codificación VBA. Son parte importante de cualquier informe o tablero, en Excel es fácil crear tablas con un botón pero en VBA tenemos que escribir algunos códigos para automatizar nuestra tabla dinámica, antes de Excel 2007 y sus versiones anteriores en VBA no necesitábamos cree un caché para tablas dinámicas, pero en Excel 2010 y sus versiones más recientes se requieren cachés.

VBA puede ahorrarnos mucho tiempo en nuestro lugar de trabajo, aunque dominarlo no es tan fácil, pero vale la pena dedicar tiempo a aprender esto. Me tomó 6 meses comprender el proceso de creación de tablas dinámicas a través de VBA. Sabes lo que esos 6 meses han hecho maravillas para mí porque cometí muchos errores al intentar crear la tabla dinámica.

Pero lo cierto es que he aprendido de mis errores y ahora estoy escribiendo este artículo para mostrarte cómo crear tablas dinámicas usando código.

Con solo hacer clic en un botón, podemos crear informes.

Pasos para crear una tabla dinámica en VBA

Puede descargar esta plantilla de tabla dinámica de VBA aquí - Plantilla de tabla dinámica de VBA

Para crear una tabla dinámica, es importante tener datos. Para esto he creado algunos datos ficticios, puede descargar el libro de trabajo para seguir conmigo con los mismos datos.

Paso 1: la tabla dinámica es un objeto para hacer referencia a la tabla dinámica y declarar la variable como tablas dinámicas.

Código:

 Sub PivotTable () Dim PTable As PivotTable End Sub

Paso 2: Antes de crear una tabla dinámica, primero debemos crear una caché dinámica para definir la fuente de los datos.

En la tabla dinámica de la hoja de trabajo normal, sin molestarnos, crearemos un caché dinámico en segundo plano. Pero en VBA tenemos que crear.

Para esto, defina la variable PivotCache.

Código:

 Atenuar PCache como PivotCache

Paso 3: Para determinar el rango de datos pivote, defina la variable como un rango.

Código:

 Dim PRange como rango

Paso 4: Para insertar una tabla dinámica, necesitamos una hoja separada para agregar una hoja de trabajo para que la tabla dinámica declare la variable como una hoja de trabajo.

Código:

 Atenuar hoja PS como hoja de trabajo

Paso 5: De manera similar, para hacer referencia a los datos que contienen la hoja de trabajo, declare una variable más como Hoja de trabajo.

Código:

 Dim DSheet como hoja de trabajo

Paso 6: Finalmente, para encontrar la última fila y columna utilizada, defina dos variables más como Long.

Código:

 Dim LR tan largo Dim LC tan largo

Paso 7: Ahora necesitamos insertar una nueva hoja para crear una tabla dinámica. Antes de eso, si hay alguna hoja dinámica, debemos eliminarla.

Paso 8: Ahora configure la variable de objeto PSheet y DSheet en Pivot Sheet y Data Sheet respectivamente.

Paso 9: busque la última fila utilizada y la última columna utilizada en la hoja de datos.

Paso 10: Ahora configure el rango de pivote usando la última fila y la última columna.

Esto establecerá el rango de datos perfectamente. Automáticamente seleccionará el rango de datos incluso si hay alguna adición o eliminación de datos en la hoja de datos.

Paso 11: Antes de crear una tabla dinámica, necesitamos crear una caché dinámica. Establezca la variable de caché dinámica utilizando el siguiente código VBA.

Paso 12: Ahora cree una tabla dinámica en blanco.

Paso 13: Después de insertar la tabla dinámica, primero debemos insertar el campo de fila. Entonces insertaré el campo de la fila como mi columna País.

Nota: descargue el libro de trabajo para comprender las columnas de datos.

Paso 14: Ahora insertaré un elemento más en el campo de fila como segundo elemento de posición. Insertaré el Producto como el segundo elemento de línea en el campo de fila.

Paso 15: Después de insertar las columnas en el campo de la fila, necesitamos insertar valores en el campo de la columna. Insertaré el "Segmento" en el campo de la columna.

Paso 16: Ahora necesitamos insertar números en el campo de datos. Así que inserte "Ventas" en el campo de datos.

Paso 17: Hemos terminado con la parte de resumen de la tabla dinámica, ahora necesitamos formatear la tabla. Para formatear la tabla dinámica, use el siguiente código.

Nota: Para tener más estilos de tabla diferentes, regístrelos macro y obtenga los estilos de tabla.

Para mostrar los elementos de valores archivados de fila en forma tabular, agregue el siguiente código en la parte inferior.

Ok, terminamos si ejecutamos este código usando la tecla F5 o manualmente, deberíamos obtener la tabla dinámica como esta.

De esta manera, utilizando la codificación VBA, podemos automatizar el proceso de creación de una tabla dinámica.

Para su referencia, le he dado el código a continuación.

 Sub PivotTable() Dim PTable As PivotTable Dim PCache As PivotCache Dim PRange As Range Dim PSheet As Worksheet Dim DSheet As Worksheet Dim LR As Long Dim LC As Long On Error Resume Next Application.DisplayAlerts = False Application.ScreenUpdating = False Worksheets("Pivot Sheet").Delete 'This will delete the exisiting pivot table worksheet Worksheets.Add After:=ActiveSheet ' This will add new worksheet ActiveSheet.Name = "Pivot Sheet" ' This will rename the worksheet as "Pivot Sheet" On Error GoTo 0 Set PSheet = Worksheets("Pivot Sheet") Set DSheet = Worksheets("Data Sheet") 'Find Last used row and column in data sheet LR = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LC = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column 'Set the pivot table data range Set PRange = DSheet.Cells(1, 1).Resize(LR, LC) 'Set pivot cahe Set PCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=PRange) 'Create blank pivot table Set PTable = PCache.CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), TableName:="Sales_Report") 'Insert country to Row Filed With PSheet.PivotTables("Sales_Report").PivotFields("Country") .Orientation = xlRowField .Position = 1 End With 'Insert Product to Row Filed & position 2 With PSheet.PivotTables("Sales_Report").PivotFields("Product") .Orientation = xlRowField .Position = 2 End With 'Insert Segment to Column Filed & position 1 With PSheet.PivotTables("Sales_Report").PivotFields("Segment") .Orientation = xlColumnField .Position = 1 End With 'Insert Sales column to the data field With PSheet.PivotTables("Sales_Report").PivotFields("Sales") .Orientation = xlDataField .Position = 1 End With 'Format Pivot Table PSheet.PivotTables("Sales_Report").ShowTableStyleRowStripes = True PSheet.PivotTables("Sales_Report").TableStyle2 = "PivotStyleMedium14" 'Show in Tabular form PSheet.PivotTables("Sales_Report").RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub