Trucos y tretas en Excel VBA para programadores (Tablas pivote)

Cuando hablamos de hacer macros de Excel, observamos que crear una tabla pivote usando código de VBA es un gran dolor de cabeza. 

Si pretendes aplicaciones con VBA en Excel este código genérico te puede facilitar las cosas, código genérico que sirve para crear una tabla pivote.

El código toma los datos de la hoja activa y crea una hoja nueva con la tabla pivote.  Permite construir una tabla con un campo en fila, uno en la columna y un campo de datos, y de manera opcional permite agregar hasta dos filtros con sus respectivos valores, y una fila adicional. Sirve en Excel 2013.  No estoy seguro de que sirva en versiones anteriores.

Quizás el código necesite algo de depuración para hacerlo a prueba de usuarios, pero es un buen punto de partida.

Primero tendrás que agregar esto al inicio del módulo:

Dim wsTarget As Worksheet
Dim rngSource As Range
Dim pc As PivotCache
Dim pt As PivotTable
Dim field As PivotField
Dim lrow As Long
Dim lcol As Long

Copia y pega el siguiente código en el módulo.

'Crear tabla pivote en hoja activa
Sub CrearPivotTable2013(sSheet As String, sDataField As String, sColumnField1 As String, sRowField1 As String, _
    Optional sRowField2 As String = "", _
    Optional sFilterField1 As String = "", Optional vFilterValue1 As Variant = "", _
    Optional sFilterField2 As String = "", Optional vFilterValue2 As Variant = "")

'ESTRUCTURA DE LA TABLA PIVOTE
'DATOS (Usa Count para los datos):
'* sDataField
'COLUMNA
'* sColumnField1
'FILA
'* sRowField1
'* sRowField2 (opcional)
'FILTRO
'* sFilterField1 = vFilterValue1 (opcional)
'* sFilterField2 = vFilterValue2 (opcional)

sPivotName = "PivotTable1"
lrow = ActiveSheet.Cells(Application.Rows.Count, 1).End(xlUp).Row
lcol = ActiveSheet.Cells(1, Application.Columns.Count).End(xlToLeft).Column

Set rngSource = ActiveSheet.Cells(1, 1).Resize(lrow, lcol)

Set wsTarget = Sheets(sSheet)

'Limpia pivotes  en la hoja actual
wsTarget.Select
For Each pt In wsTarget.PivotTables
    wsTarget.Range(pt.TableRange2.Address).Delete Shift:=xlUp
Next pt

'Crea tabla pivote (falta depurar porque a veces da errores de "type mismatch" en la linea que asigna el objeto  "pc")
Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, rngSource, xlPivotTableVersion15)
Set pt = pc.CreatePivotTable(wsTarget.Range("A3"), sPivotName, , xlPivotTableVersion15)

'Columna
Set field = wsTarget.PivotTables(sPivotName).PivotFields(sColumnField1)
field.Orientation = xlColumnField
field.Position = 1

'Fila
Set field = wsTarget.PivotTables(sPivotName).PivotFields(sRowField1)
field.Orientation = xlRowField
field.Position = 1

'Fila (Opcional)
If sRowField2 <> "" Then
Set field = wsTarget.PivotTables(sPivotName).PivotFields(sRowField2)
field.Orientation = xlRowField
field.Position = 2
End If

'Filtros (Opcional)
If sFilterField1 <> "" Then
    Set field = wsTarget.PivotTables(sPivotName).PivotFields(sFilterField1)
    field.Orientation = xlPageField
    field.Position = 1
    field.ClearAllFilters
    field.CurrentPage = vFilterValue1
End If

If sFilterField2 <> "" Then
    Set field = wsTarget.PivotTables(sPivotName).PivotFields(sFilterField2)
    field.Orientation = xlPageField
    field.Position = 2
    field.ClearAllFilters
    field.CurrentPage = vFilterValue2
End If

'Datos
Set field = wsTarget.PivotTables(sPivotName).PivotFields(sDataField)
Set field = wsTarget.PivotTables(sPivotName).AddDataField(field, "Count of " & sDataField, xlCount)

End Sub

Si deseas que sume los datos en lugar de contarlos, usa xlSum en lugar de xlCount.

¿Cómo usar este código? 

Tienes la gran tabla con los datos, en la hoja actual.  Vas a crear tabla pivote en una nueva hoja llamada "HojaPivote" (si ya existe y contiene una tabla pivote con datos desactualizados, no te preocupes, que este código se encarga de eliminar los pivotes existentes).  La hoja actual con datos contiene varias columnas en cuyo encabezado en la fila número 1 se lee lo siguiente:

• Datos
• Columna
• Fila
• Fila2
• Filtro1
• Filtro2

Los encabezados de columna listados anteriormente no necesariamente deben estar em ese orden en la hoja actual, pueden estar en cualquier columna.  Estos valores que he listado los he puesto para que sepas cuales son los encabezados de columna que necesitarás.

En la columna de Filtro1 algunas celdas contienen el valor "valor de filtro 1".  En la columna de Filtro2 algunas celdas contienen el valor "valor de filtro 2".

Crear una tabla básica con un campo de fila, columna y datos.

CrearPivotTable2013 "HojaPivote", "Datos", "Columna", "Fila"

Ejemplo: Crea una tabla que cuenta la cantidad de empleados por categoría salarial según sexo.

CrearPivotTable2013 "MiHoja", "Número de Empleado", "Categoría salarial", "Sexo"

Crear una tabla con dos campos de fila, una columna, dos filtros y datos.

CrearPivotTable2013 "HojaPivote", "Datos", "Columna", "Fila", "Fila2", "Filtro1", "Valor de filtro 1", "Filtro2", "Valor de filtro 2"

Ejemplo:  Igual que el ejemplo anterior, pero sólo para el mes de "Diciembre".

CrearPivotTable2013 "MiHoja", "Número de Empleado", "Categoría salarial", "Sexo", , "Mes", "Diciembre"

Espero que te sirva.