Crear una tabla dinámica con VBA

Crear una tabla dinámica a través de una macro es una de esas tareas que siempre será más fácil y mejor si se digita el código que si se hace usando la grabadora de macros. A continuación explico un ejemplo para crear una tabla dinámica.

El proceso que seguiremos es el siguiente:

1. Iniciar el procedimiento y definir las variables

Sub CrearTablaD() 'Iniciar el procedimiento Dim hoja As Worksheet 'Hoja en que se creará la tabla dinámica Dim PivotC As PivotCache 'Variable que almacena la memoria caché de la tabla Dim mi_tabla As PivotTable 'Nombre que se le da a la tabla dinámica Dim i As Integer 'Variable que servirá para contar el número de datos, en especial hojas en el libro Dim respuesta As Integer 'Almacena el valor del botón presionado por el usuario

Nota: Si existen varias tablas dinámicas, la memoria caché dinámica puede reutilizarse como almacén de datos interno para cada una de las tablas dinámicas.

2. Verificar si existe la hoja Tabla Dinamica que es aquella donde se creará la nueva tabla. El código crea una hoja nueva y le coloca el nombre Tabla Dinamica. Obviamente, si la hoja ya existe, la macro dará como resultado un error pues no pueden existir dos hojas con el mismo nombre, razón por la cual, en caso de existir se le pregunta al usuario si la desea eliminar o no.

En este paso también se cancela el alerta de eliminación de la hoja, en caso de que se decida eliminarla, o se le pide al usuario que cambie el nombre a la hoja, en caso de que decida no hacer la eliminación.

Application.DisplayAlerts = False 'Cancela las alertas del sistema 'Revisa si existe la hoja Tabla Dinamica (pues la macro crea una nueva hoja y 'la llama así), pregunta al usuario si desea eliminarla For i = 1 To Worksheets.Count 'Recorrer las hojas desde la primera hasta el total de hojas del libro If Worksheets(i).Name = "Tabla Dinamica" Then 'Si el nombre de la hoja es Tabla Dinamica entonces respuesta = MsgBox("La hoja Tabla Dinamica ya existe debe eliminarla si quiere continuar. Desea eliminarla? ", vbYesNo, "Error") 'Aparece el mensaje en pantalla con los botones Sí y No If respuesta = 6 Then 'Si el usuario presiona el botón Sí (el valor de este botón es 6) entonces Worksheets(i).Delete 'Se elimina la hoja Tabla Dinamica Else 'En otro caso, es decir, si presiona el botón No (cuyo valor es 7) MsgBox "La macro no puede ejecutarse, debe eliminar la hoja Tabla Dinamica o cambiarle el nombre" End If End 'Cierra el procedimiento End If Next 'Pasa a la siguiente hoja

3. Adiciona una hoja, la llama Tabla Dinamica y se ubica en ella. Este es un paso que se puede obviar si se desea usar una hoja ya existente en lugar de crear una hoja nueva.

Sheets.Add 'Crea una hoja nueva ActiveSheet.Name = "Tabla Dinamica" 'Cambia el nombre a la hoja creada por Tabla Dinamica Worksheets("Tabla Dinamica").Activate 'Activa la hoja Tabla Dinamica

4. Preparar las variables del entorno necesarias y crear la tabla dinámica. Para ésto se necesita definir lo normal para crear cualquier tabla dinámica: el destino de la tabla, el origen de los datos y otra no tan común como definir el caché de la tabla.

Set hoja = Worksheets("Tabla Dinamica") 'Se define la hoja de destino 'Se crea la memoria cache de la TD (Pivot cache), usando la base de datos nombrada Tabla_Datos Set PivotC = ActiveWorkbook.PivotCaches.Create(xlDatabase, "Tabla_datos") 'Genera la TD en la hoja tabla dinámica en la celda A3 y se le da el nombre de mi_tabla Set mi_tabla = PivotC.CreatePivotTable(hoja.Range("A3"))

5. Se configura la tabla dinámica, es decir, se colocan los campos de fila, columna, filtros y se realizan los cálculos necesarios.

With mi_tabla 'Para la tabla dinámica 'Este es el código para colocar un campo en el área filas .PivotFields("CiudadC").Orientation = xlRowField 'Este es el código para colocar un campo en el área columnas .PivotFields("RegimenC").Orientation = xlColumnField 'Este es el código para colocar un campo en el área de filtro .PivotFields("SectorC").Orientation = xlPageField 'Este es el código para colocar un campo en el área de datos o valores .PivotFields("EmailC").Orientation = xlDataField 'Este es el código para colocar un segundo campo en el área de datos o valores .PivotFields("Ventas").Orientation = xlDataField End With End Sub 'Se termina el procedimiento

El ejemplo funcional de este tema se puede descargar en el siguiente enlace: Tablas_dinamicas