VBA Guía para Tablas Dinámicas

Este tutorial mostrará cómo trabajar con tablas dinámicas utilizando VBA Las tablas dinámicas son herramientas de resumen de datos que puede utilizar para obtener información clave y resúmenes de sus datos. Veamos un ejemplo: tenemos un conjunto de datos de origen en las celdas A1:D21 que contienen los detalles de los productos vendidos, mostrados a continuación:

 

Uso de GetPivotData para obtener un valor

Supongamos que tiene una Tabla Dinámica llamada Tabladinamica1 con Monto en el campo Valores/Datos, Item como campo Filas y Cliente como campo Columnas. Puede utilizar el método PivotTable.GetPivotData para devolver valores de las tablas dinámicas. El siguiente código devolverá 240,00 dólares (el total de ventas del cliente Carrie) desde la tabla dinámica:

MsgBox ActiveCell.PivotTable.GetPivotData("Monto", "Cliente", "Carrie")

En este caso, Monto es el «DataField», «Field1» es el Cliente y «Item1» es Carrie. El siguiente código devolverá $150 (el total de ventas del Item Top Epic) desde la Tabla Dinámica:

MsgBox ActiveCell.PivotTable.GetPivotData("Monto", "Item", "Top Epic")

En este caso, Monto es el «DataField», «Field1» es Item, «Item1» es Top Epic. También puede incluir más de 2 campos. La sintaxis de GetPivotData es: GetPivotData(DataField, Field1, Item1, Field2, Item2…) donde:

Parámetro	Descripción
DataField	Campo de datos como Monto, Precio, cantidad, etc. que contiene números.
Field1	Nombre de un campo de columna o fila en la tabla.
Item1	Nombre de un artículo en el campo 1 (opcional).
Field2	Nombre de un campo de columna o fila en la tabla (Opcional).
Item2	Nombre de un elemento en el Campo 2 (Opcional).

Creación de una tabla dinámica en una hoja

Para crear una tabla dinámica basada en el rango de datos anterior, en la celda A20 de la Hoja2 del libro de trabajo activo , utilizaríamos el siguiente código:

Sub CrearTablaDinamica()
    
    Worksheets("Hoja2").Cells(20, 1).Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
    SourceData:="Hoja2!R1C1:R18C7", Version:=xlPivotTableVersion15).CreatePivotTable _
    TableDestination:="Hoja2!R20C1", TableName:="PivotTable3", DefaultVersion:=xlPivotTableVersion15
 
    Sheets("Hoja2").Select
End Sub

El resultado es:

Creación de una tabla dinámica en una nueva hoja

Para crear una tabla dinámica basada en el rango de datos anterior, en una nueva hoja, del libro de trabajo activo, utilizaríamos el siguiente código:

Sub CrearTablaDinamicaEnNuevaHoja()
    Worksheets("Hoja2").Cells(1, 1).Select
 
    Sheets.Add
 
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Hoja2!R1C1:R18C7", _
    Version:=xlPivotTableVersion15).CreatePivotTable _
    TableDestination:="Hoja3!R1C1", TableName:="PivotTable4", DefaultVersion:=xlPivotTableVersion15
 
    Sheets("Hoja3").Select
End Sub

Añadir campos a la tabla dinámica

Puede añadir campos a la tabla dinámica recién creada, llamada PivotTable1, basándose en el rango de datos anterior. Nota: La hoja que contiene la tabla dinámica debe ser la hoja activa. Para añadir el Item al Campo de Filas, debe utilizar el siguiente código:

Sub adicionarCamposaTablaDinamica()
    ActiveSheet.PivotTables("PivotTable3").PivotFields("Item").Orientation = xlRowField
    ActiveSheet.PivotTables("PivotTable3").PivotFields("Item").Position = 1
End Sub

Para añadir el Cliente al Campo de las Columnas, se utilizaría el siguiente código:

Sub adicionarCamposColumnas()
    ActiveSheet.PivotTables("PivotTable3").PivotFields("Cliente").Orientation = xlColumnField
    ActiveSheet.PivotTables("PivotTable3").PivotFields("Cliente").Position = 1
End Sub

Para añadir Ventas a la Sección de Valores con el formato de número de moneda, se utilizaría el siguiente código:

Sub adicionarValoresaTabaDinamica()
    ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables("PivotTable3").PivotFields("Monto"), _
    "Suma de Ventas", xlSum
 
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Suma de Ventas")
        .NumberFormat = "$#,##0.00"
    End With
End Sub

El resultado es:

Cambiar el diseño del informe de la tabla dinámica

Puede cambiar el diseño del informe de su tabla dinámica. El siguiente código cambiará el diseño del informe de la tabla dinámica a un formato tabular:

Sub cambiarDisenoTabla()
    ActiveSheet.PivotTables("PivotTable3").TableStyle2 = "PivotStyleLight18"
End Sub

Eliminación de una tabla dinámica

Puede eliminar una tabla dinámica utilizando VBA. El siguiente código eliminará la tabla dinámica llamada PivotTable1 en la hoja activa:

Sub eliminarTablaDinamica()
    ActiveSheet.PivotTables("PivotTable3").PivotSelect "", xlDataAndLabel, True
    Selection.ClearContents
End Sub

Formatear todas las Tablas Dinámicas de un Libro de Trabajo

Puede formatear todas las Tablas Dinámicas de un Libro de Trabajo utilizando VBA. El siguiente código utiliza una estructura de bucle para recorrer todas las hojas de un libro de trabajo y formatear todas las tablas dinámicas del libro:

Sub DarFormatoaTodasLasTablasDinamicasEnUnLibro()
    Dim hoja As Worksheet
    Dim libro As Workbook
    Set libro = ActiveWorkbook
    Dim tDinamica As PivotTable
    For Each hoja In libro.Sheets
        For Each tDinamica In hoja.PivotTables
            tDinamica.TableStyle2 = "PivotStyleLight15"
        Next tDinamica
    Next hoja
End Sub

Para aprender más sobre cómo utilizar los bucles en VBA haga clic aquí.

Eliminación de Campos de una Tabla Dinámica

Puede eliminar los campos de una Tabla Dinámica utilizando VBA. El siguiente código eliminará el campo Item en la sección Filas de una Tabla Dinámica llamada PivotTable3 en la Hoja Activa:

ActiveSheet.PivotTables("PivotTable3").PivotFields("Item").Orientation = xlHidden

Creación de un filtro

Se ha creado una tabla dinámica llamada PivotTable3 con Item en la sección Filas, y Monto en la sección Valores. También puede crear un filtro para su tabla dinámica utilizando VBA. El siguiente código creará un filtro basado en el Cliente en la sección Filtros:

ActiveSheet.PivotTables("PivotTable3").PivotFields("Cliente").Orientation = xlPageField
 
ActiveSheet.PivotTables("PivotTable3").PivotFields("Cliente").Position = 1

Para filtrar la tabla dinámica basándose en un único elemento del informe, en este caso la región Este, se utilizaría el siguiente código:

ActiveSheet.PivotTables("PivotTable3").PivotFields("Cliente").ClearAllFilters

ActiveSheet.PivotTables("PivotTable3").PivotFields("Cliente").CurrentPage = "Carrie"

Digamos que quieres filtrar tu tabla dinámica en base a múltiples regiones, en este caso Este y Norte, utilizarías el siguiente código:

Sub filtrarTablaVariosCampos()
    
    ActiveSheet.PivotTables("PivotTable3").PivotFields("Cliente").Orientation = xlPageField
    ActiveSheet.PivotTables("PivotTable3").PivotFields("Cliente").Position = 1
    ActiveSheet.PivotTables("PivotTable3").PivotFields("Cliente").EnableMultiplePageItems = True
     
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Cliente")
        .PivotItems("Carrie").Visible = False
        .PivotItems("Gledys").Visible = False
    End With
End Sub

Actualizar la tabla dinámica

Puede actualizar su tabla dinámica en VBA. Utilizaría el siguiente código para actualizar una tabla específica llamada PivotTable1 en VBA:

ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh