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, vale la pena dedicar tiempo a aprenderlo. 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
Para crear una tabla dinámica, es importante tener datos. Para ello, 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 como tabla dinámica End Sub
Paso 2: antes de crear una tabla dinámica primero, necesitamos crear una caché dinámica para definir la fuente de los datos.
En las hojas de trabajo normales, la tabla dinámica sin molestarnos creará una caché dinámica en segundo plano. Pero en VBA, tenemos que crear.
Para ello, defina la variable a 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 hojas 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. Seleccionará automáticamente 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 la 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 la fila como segundo elemento de posición. Insertaré el Producto como 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. Por tanto, 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.
Para mostrar los elementos de valores de filas archivados en forma tabular, agregue el código siguiente 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
