¿cómo crear una tabla dinámica con vba?
Antes de entregarte esta guía y comenzar a usar VBA para crear una tabla dinámica , déjame decirte algo.
Aprendí a usar VBA hace apenas SEIS años. Y la primera vez que escribí código de macro para crear una tabla dinámica, fue un fracaso.
Desde entonces, he aprendido más de mi mala codificación que de códigos que realmente funcionan.
Hoy les mostraré una forma sencilla de automatizar sus tablas dinámicas usando código de macro.
Normalmente, cuando insertas una tabla dinámica en una hoja de cálculo, ocurre mediante un proceso simple, pero todo este proceso es tan rápido que nunca te das cuenta de lo que sucedió.
En VBA todo este proceso es el mismo, simplemente se ejecuta usando código. En esta guía, le mostraré cada paso y le explicaré cómo escribir código.
Simplemente mire el ejemplo a continuación, donde puede ejecutar este código de macro con un botón y devuelve una nueva tabla dinámica en una nueva hoja de trabajo en un instante.
Sin más preámbulos, comencemos a escribir nuestro código de macro para crear una tabla dinámica.
Los 8 sencillos pasos para escribir código de macro en VBA para crear una tabla dinámica en Excel
Para tu comodidad, he dividido todo el proceso en 8 sencillos pasos. Después de seguir estos pasos, podrá automatizar todas sus tablas dinámicas.
Asegúrese de descargar este archivo desde aquí para seguirlo.
1. Declarar variables
El primer paso es declarar las variables que necesitamos usar en nuestro código para definir diferentes cosas.
'Declare Variables Dim PSheet As Worksheet Dim DSheet As Worksheet Dim PCache As PivotCache Dim PTable As PivotTable Dim PRange As Range Dim LastRow As Long Dim LastCol As Long
En el código anterior declaramos:
- PSheet: para crear una hoja para una nueva tabla dinámica.
- DHoja: para utilizar como ficha técnica.
- PChache: Úselo como nombre para la caché de la tabla dinámica.
- PTable: Úselo como nombre para nuestra tabla dinámica.
- PRange: para establecer el rango de datos de origen.
- LastRow y LastCol: para obtener la última fila y columna de nuestro rango de datos.
2. Inserta una nueva hoja de cálculo.
Antes de crear una tabla dinámica, Excel inserta una hoja en blanco y luego crea una nueva tabla dinámica en ella.
Y el siguiente código hará lo mismo por usted.
Insertará una nueva hoja de trabajo con el nombre «Tabla dinámica» antes de la hoja de trabajo activa y, si ya existe una hoja de trabajo con el mismo nombre, la eliminará primero.
Después de insertar una nueva hoja de trabajo, este código establecerá el valor de la variable PSheet en la hoja de trabajo de la tabla dinámica y DSheet en la hoja de trabajo de datos de origen.
'Declare Variables On Error Resume Next Application.DisplayAlerts = False Worksheets("PivotTable").Delete Sheets.Add Before:=ActiveSheet ActiveSheet.Name = "PivotTable" Application.DisplayAlerts = True Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets("Data")
Asegúrese de reemplazar los nombres de las hojas de trabajo en el código con los nombres que tiene en sus datos.
3. Establecer rango de datos
Ahora el siguiente paso es establecer el rango de datos de la hoja de trabajo de origen. Aquí hay que tener en cuenta una cosa: no se puede especificar un rango de fuente fijo.
Necesita un código que pueda identificar todos los datos en la hoja fuente. Y a continuación está el código:
'Define Data Range LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)
Este código comenzará desde la primera celda de la tabla de datos y seleccionará hasta la última fila, luego hasta la última columna.
Y finalmente, establezca este rango seleccionado como fuente. La mejor parte es que no necesita cambiar la fuente de datos cada vez que crea la tabla dinámica.
4. Cree una caché dinámica
En Excel 2000 y versiones posteriores, antes de crear una tabla dinámica, debe crear una caché dinámica para configurar la fuente de datos.
Normalmente, cuando crea una tabla dinámica, Excel crea automáticamente un caché dinámico sin preguntarle, pero cuando necesita usar VBA, debe escribir algún código para ello.
'Define Pivot Cache Set PCache = ActiveWorkbook.PivotCaches.Create _ (SourceType:=xlDatabase, SourceData:=PRange). _ CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _ TableName:="SalesPivotTable")
Este código funciona de dos maneras: primero, configurando un caché dinámico usando una fuente de datos y, segundo, configurando la dirección de la celda en la hoja de trabajo recién insertada para insertar la tabla dinámica.
Puede cambiar la posición de la tabla dinámica modificando este código.
5. Inserte una tabla dinámica en blanco
Después del caché dinámico, el siguiente paso es insertar una tabla dinámica vacía. Solo recuerde que cuando crea una tabla dinámica, siempre obtiene primero una tabla dinámica vacía y luego establece todos los valores, columnas y filas.
Este código hará lo mismo:
'Insert Blank Pivot Table Set PTable = PCache.CreatePivotTable _ (TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable")
Este código crea una tabla dinámica vacía y la denomina «SalesPivotTable». Puede cambiar este nombre desde el propio código.
6. Insertar campos de filas y columnas.
Después de crear una tabla dinámica vacía, el siguiente paso es insertar campos de filas y columnas, como lo hace normalmente.
Para cada campo de fila y columna, debes escribir un código. Aquí queremos agregar años y meses en el campo de fila y áreas en el campo de columna.
Aquí está el código:
'Insert Row Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Year") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Month") .Orientation = xlRowField .Position = 2 End With 'Insert Column Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Zone") .Orientation = xlColumnField .Position = 1 End With
En este código ha mencionado año y mes como dos campos. Ahora, si observa el código, verá que también hay un número de posición. Este número de posición define la secuencia de campos.
Cada vez que necesite agregar varios campos (Fila o Columna), especifique su posición. Y puedes modificar los campos cambiando su nombre en el código.
7. Insertar un campo de datos
Lo principal es definir el campo de valor en su tabla dinámica.
El código para definir valores se diferencia de definir filas y columnas porque aquí necesitamos definir el formato de números, posiciones y funciones.
'Insert Data Field With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Amount") .Orientation = xlDataField .Function = xlSum .NumberFormat = "#,##0" .Name = "Revenue " End With
Puede agregar el monto como un campo de valor con el código de arriba. Y este código formateará los valores como un número con un separador (,).
Usamos xlsum para sumar los valores, pero también puedes usar xlcount y otras funciones.
8. Formatee la tabla dinámica
En última instancia, necesita usar código para formatear su tabla dinámica. Generalmente hay un formato predeterminado en una tabla dinámica, pero puede cambiar este formato.
Con VBA, puedes configurar el estilo de formato en el código.
El código es:
'Format Pivot TableActiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = True ActiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9"
El código anterior aplicará tiras de líneas y el estilo «Pivot Style Medium 9», pero también puedes usar otro estilo desde este enlace .
Finalmente, su código está listo para usar.
[CÓDIGO COMPLETO] Utilice VBA para crear una tabla dinámica en Excel: copiar y pegar macro
Sub InsertPivotTable() 'Macro By Exceladvisor.org 'Declare Variables Dim PSheet As Worksheet Dim DSheet As Worksheet Dim PCache As PivotCache Dim PTable As PivotTable Dim PRange As Range Dim LastRow As Long Dim LastCol As Long 'Insert a New Blank Worksheet On Error Resume Next Application.DisplayAlerts = False Worksheets("PivotTable").Delete Sheets.Add Before:=ActiveSheet ActiveSheet.Name = "PivotTable" Application.DisplayAlerts = True Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets("Data") 'Define Data Range LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol) 'Define Pivot Cache Set PCache = ActiveWorkbook.PivotCaches.Create _ (SourceType:=xlDatabase, SourceData:=PRange). _ CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _ TableName:="SalesPivotTable") 'Insert Blank Pivot Table Set PTable = PCache.CreatePivotTable _ (TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable") 'Insert Row Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Year") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Month") .Orientation = xlRowField .Position = 2 End With 'Insert Column Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Zone") .Orientation = xlColumnField .Position = 1 End With 'Insert Data Field With ActiveSheet.PivotTables("SalesPivotTable").PivotFields ("Amount") .Orientation = xlDataField .Function = xlSum .NumberFormat = "#,##0" .Name = "Revenue " End With 'Format Pivot Table ActiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = True ActiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9" End Sub
Descargar un archivo de muestra
Tabla dinámica en una hoja de trabajo existente
El código que usamos anteriormente crea una tabla dinámica en una nueva hoja de trabajo, pero a veces es necesario insertar una tabla dinámica en una hoja de trabajo que ya está en el libro.
En el código anterior (tabla dinámica en una nueva hoja de trabajo), en la parte donde escribió el código para insertar una nueva hoja de trabajo, asígnele un nombre. Realice algunos cambios en el código.
No te preocupes; Yo te mostraré.
Primero debe especificar la hoja de trabajo (ya en el libro de trabajo) donde desea insertar su tabla dinámica.
Y para esto, debes usar el siguiente código:
En lugar de insertar una nueva hoja de trabajo, debe especificar el nombre de la hoja de trabajo en la variable PSheet.
Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets(“Data”)
Hay un poco más por hacer. El primer código que utilizó elimina la hoja de trabajo del mismo nombre (si existe) antes de insertar el pivote.
Cuando inserta una tabla dinámica en la hoja de trabajo existente, es posible que ya tenga allí una tabla dinámica con el mismo nombre.
Lo que digo es que primero debes quitar ese pivote.
Para hacer esto, debe agregar el código que debería eliminar el pivote del mismo nombre de la hoja de trabajo (si está allí) antes de insertar uno nuevo.
Aquí está el código que necesitas agregar:
Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets(“Data”) Worksheets("PivotTable").Activate On Error Resume Next ActiveSheet.PivotTables("SalesPivotTable").TableRange2.Clear
Déjame decirte qué hace este código.
Primero, simplemente configura PSheet como la hoja de trabajo en la que desea insertar la tabla dinámica que ya está en su libro de trabajo y configura las hojas de trabajo de datos como DSheet.
Después de eso, activa la hoja de trabajo y elimina la «Tabla dinámica de ventas».
Importante: si los nombres de las hojas de trabajo de su libro de trabajo difieren, puede cambiarlos desde el código. He resaltado el código donde necesitas hacer esto.
Al final,
Usando este código podemos automatizar sus tablas dinámicas. Y la mejor parte es que es una configuración única; después de eso, solo necesitamos un clic para crear una tabla dinámica y podrá ahorrar mucho tiempo. Ahora dime una cosa.
¿Alguna vez ha utilizado código VBA para crear una tabla dinámica?
Por favor comparta sus opiniones conmigo en el cuadro de comentarios; Me gustaría compartirlos contigo y compartir este consejo con tus amigos.
Artículos similares: