VBA para crear una TABLA PIVOTANTE en Excel

Si eres un usuario de Excel y te gustaría optimizar tus análisis de datos de una manera más eficiente, has llegado al lugar correcto. En este artículo, te enseñaremos cómo utilizar VBA para crear una tabla pivotante en Excel. Con esta herramienta poderosa, podrás resumir y analizar grandes cantidades de información de una manera sencilla y rápida. ¡Sigue leyendo para descubrir los pasos clave para aprovechar al máximo esta función y mejorar tu flujo de trabajo en Excel!

Antes de entregarte esta guía y comenzar a usar VBA para crear una tabla dinámica Déjame confesarte algo.

Aprendí a usar VBA hace apenas SEIS años. Y la primera vez que escribí un 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 los códigos que realmente funcionan.

Hoy, le mostraré una forma sencilla de automatizar sus tablas dinámicas utilizando un código de macro.

Normalmente, cuando inserta una tabla dinámica en una hoja de trabajo, se realiza mediante un proceso simple, pero todo ese proceso es tan rápido que nunca te das cuenta de lo que pasó.

En VBA, Todo el proceso es el mismo, solo se ejecuta usando un código. En esta guía, te mostraré cada paso. y explique cómo escribir un código para ello.

Simplemente mire el siguiente ejemplo, 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.

VBA para crear una TABLA PIVOTANTE en Excel

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 un 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 las 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, hemos declarado:

  1. PSheet: para crear una hoja para una nueva tabla dinámica.
  2. DSheet: Para utilizar como hoja de datos.
  3. PChache: para utilizar como nombre para la caché de la tabla dinámica.
  4. PTable: Para usar como nombre para nuestra tabla dinámica.
  5. PRange: para definir el rango de datos de origen.
  6. LastRow y LastCol: para obtener la última fila y columna de nuestro rango de datos.

2. Inserte una nueva hoja de trabajo

Antes de crear una tabla dinámica, Excel inserta una hoja en blanco y luego crea una nueva tabla dinámica allí.

VBA para crear una TABLA PIVOTANTE en Excel

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 cambiar el nombre de las hojas de trabajo en el código por los nombres que tiene en sus datos.

3. Definir rango de datos

Ahora, lo siguiente es definir el rango de datos de la hoja de trabajo de origen. Aquí debes encargarte de una cosa: no puedes especificar un rango de fuente fijo.

Necesita un código que pueda identificar todos los datos de 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 y luego hasta la última columna.

Y finalmente, define ese rango seleccionado como fuente. La mejor parte es que no es necesario cambiar la fuente de datos cada vez que se 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 definir 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 un código para esto.

'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, defina un caché dinámico utilizando una fuente de datos y, en segundo lugar, defina 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 editando 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 en blanco. Solo recuerde lo que sucede cuando crea una tabla dinámica: primero siempre obtiene una tabla dinámica en blanco y luego define todos los valores, columnas y filas.

VBA para crear una TABLA PIVOTANTE en Excel

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 en blanco y la denomina «SalesPivotTable». Puede cambiar este nombre desde el propio código.

6. Insertar campos de fila y columna

Después de crear una tabla dinámica en blanco, lo siguiente es insertar campos de filas y columnas, como lo hace normalmente.

Para cada campo de fila y columna, debe escribir un código. Aquí queremos agregar años y meses en el campo de fila y zonas en el campo de columna.

VBA para crear una TABLA PIVOTANTE en Excel

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, encontrará que también hay un número de posición. Este número de posición define la secuencia de campos.

Siempre que necesite agregar más de un campo (fila o columna), especifique su posición. Y puedes cambiar los campos editando su nombre desde el código.

7. Insertar campo de datos

Lo principal es definir el campo de valor en su tabla dinámica.

El código para definir valores difiere de definir filas y columnas porque aquí debemos 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 campo de valor con el código anterior. Y este código formateará los valores como un número con un separador (,).

Usamos xlsum para sumar valores, pero también puedes usar xlcount y otras funciones.

8. Formatear tabla dinámica

En última instancia, necesita utilizar un código para formatear su tabla dinámica. Normalmente hay un formato predeterminado en una tabla dinámica, pero puedes cambiar ese formato.

Con VBA, puedes definir el estilo de formato dentro del código.

VBA para crear una TABLA PIVOTANTE en Excel

El código es:

'Format Pivot
TableActiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = True
ActiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9"

El código anterior aplicará tiras de filas y el estilo “Pivot Style Medium 9”, pero también puedes usar otro estilo de este enlace.

Finalmente, su código está listo para usar.

[FULL CODE] Utilice VBA para crear una tabla dinámica en Excel: macro para copiar y pegar

Sub InsertPivotTable()
'Macro By ExcelChamps.com

'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

Tabla dinámica en la hoja de trabajo existente

El código que hemos utilizado 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 nueva hoja de trabajo), en la parte donde escribió el código para insertar una nueva hoja de trabajo y luego nombrarla. Por favor haga algunos ajustes al código.

No te preocupes; Te mostrare.

Primero debe especificar la hoja de trabajo (ya en el libro de trabajo) donde desea insertar su tabla dinámica.

Y para esto, necesitas 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 con el mismo nombre (si existe) antes de insertar el pivote.

Cuando inserta una tabla dinámica en la hoja de trabajo existente, existe la posibilidad de que ya tenga una tabla dinámica allí con el mismo nombre.

Lo que digo es que primero debes eliminar ese pivote.

Para esto, debe agregar el código que debería eliminar el pivote con el 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 donde 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 «Tabla dinámica de ventas».

Importante: Si los nombres de las hojas de trabajo en su libro de trabajo difieren, puede cambiarlos desde el código. He resaltado el código donde debes hacerlo.

Al final,

Al utilizar este código, podemos automatizar sus tablas dinámicas. Y la mejor parte es que se trata de 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 has usado un código VBA para crear una tabla dinámica?

Comparta sus opiniones conmigo en el cuadro de comentarios; Me encantaría compartirlos contigo y compartir este consejo con tus amigos.

VBA es una de las habilidades avanzadas de Excel, y si está comenzando con VBA, asegúrese de consultar los ejemplos de macros útiles y los códigos de VBA.

Error 403 The request cannot be completed because you have exceeded your quota. : quotaExceeded





VBA para crear una TABLA PIVOTANTE en Excel – Preguntas Frecuentes

VBA para crear una TABLA PIVOTANTE en Excel – Preguntas Frecuentes

¿Qué es una tabla pivote en Excel?

Una tabla pivote es una herramienta poderosa en Excel que permite resumir y analizar grandes conjuntos de datos de manera rápida y sencilla. Estas tablas son especialmente útiles para buscar insights valiosos en los datos y generar informes de manera efectiva.

¿Cómo puedo crear una tabla pivote en Excel utilizando VBA?

Para crear una tabla pivote en Excel utilizando VBA, puedes seguir los siguientes pasos:

  1. Abre Excel e ingresa a la pestaña de «Developer» (Desarrollador).
  2. Haz clic en «Visual Basic» para abrir el Editor de VBA.
  3. Escribe el código VBA para definir el rango de datos y ubicación de la tabla pivote.
  4. Utiliza la función «PivotTable.Add» para crear una nueva tabla pivote.
  5. Personaliza la tabla pivote según tus necesidades, estableciendo campos y valores.
  6. Por último, ejecuta el código VBA para crear la tabla pivote en Excel.

¿Dónde puedo encontrar ejemplos de código VBA para crear tablas pivote?

Si estás buscando ejemplos de código VBA para crear tablas pivote en Excel, puedes consultar los siguientes recursos:

¿Cuáles son los beneficios de utilizar VBA para crear tablas pivote?

Utilizar VBA para crear tablas pivote en Excel ofrece varios beneficios, entre los cuales se incluyen:

  • Automatización: VBA te permite automatizar la creación de tablas pivote, ahorrando tiempo y esfuerzo.
  • Personalización: Puedes personalizar tus tablas pivote según tus necesidades específicas, estableciendo campos, valores, filtros y más.
  • Análisis de datos avanzado: Con VBA, puedes realizar análisis de datos más avanzados, aplicar formatos condicionales y realizar cálculos complejos en tus tablas pivote.
  • Generación de informes efectiva: Las tablas pivote creadas con VBA ofrecen una manera efectiva de generar informes y presentar datos de manera clara y concisa.

¡Recuerda que practicar y experimentar con el código VBA es la mejor manera de familiarizarte con la creación de tablas pivote en Excel!

Deja un comentario