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.