jueves, 23 de julio de 2015

VBA: Crear una tabla dinámica con macros para Excel.

Sin duda una de las acciones que más fallos y errores nos puede causar es emplear el asistente de grabación de macros para replicar la creación de una tabla dinámica...
Por ello, hoy veremos una forma válida para crear y configurar nuestras tablas tablas dinámicas empleando la programación VBA para Excel.


Partiremos del siguiente origen de datos ('Tabla1'), con tres campos: 'Departamento', 'Zona' e 'Importe'.

VBA: Crear una tabla dinámica con macros para Excel.



El objetivo es crear una tabla dinámica con esta forma:

VBA: Crear una tabla dinámica con macros para Excel.



Generamos nuestro código en un módulo estándar de nuestro proyecto de VB:

Sub CrearTablaDinamica()
'www.excelforo.com
Dim ws As Worksheet
Dim pc As PivotCache
Dim pt As PivotTable

'definimos la hoja destino....
Set ws = Worksheets("Hoja2")

'Creamos la memoria cahce de la TD (Pivot cache)
Set pc = ActiveWorkbook.PivotCaches.Create(xlDatabase, "Tabla1")

'Ahora generamos la TD
Set pt = pc.CreatePivotTable(ws.Range("B3"))

'configuramos la estructura de la TD
With pt
    'llevamos al área de filas el campo 'Departamento'
    With .PivotFields("Departmento")
    .Orientation = xlRowField
    .Position = 1
    End With
    'llevamos al área de columnas el campo 'Zona'
    With .PivotFields("Zona")
    .Orientation = xlColumnField
    .Position = 1
    End With
    'y al área de valores el campo 'Importe'... también le damos formato...
    Set campo = .AddDataField(.PivotFields("Importe"), "Total", xlSum)
    campo.NumberFormat = "#,##0.00"
End With

End Sub


Estamos listos para ejecutar nuestra macro 'CrearTablaDinamica' y generar nuestra nueva Tabla dinámica...

El código generado con el asistente de grabación sería...

Sub Macro1()
'
' Macro1 Macro
'

'
    Sheets("Hoja1").Select
    Range("B9").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Tabla1", Version:=xlPivotTableVersion15).CreatePivotTable TableDestination _
        :="Hoja2!R3C2", TableName:="Tabla dinámica1", DefaultVersion:= _
        xlPivotTableVersion15
    Sheets("Hoja2").Select
    Cells(3, 2).Select
    With ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("Departmento")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("Zona")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("Tabla dinámica1").AddDataField ActiveSheet.PivotTables _
        ("Tabla dinámica1").PivotFields("Importe"), "Suma de Importe", xlSum
    With ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("Suma de Importe")
        .NumberFormat = "#.##0,00"
    End With
End Sub


Creo que no existe duda cuál de los dos códigos es más claro (y fiable!!).

11 comentarios:

  1. hola, buen día tengo una pregunta ¿Qué lineas tengo que modificar para generar a partir de este código una tabla dinámica con mis datos?

    ResponderEliminar
    Respuestas
    1. Hola,
      cambia en la línea
      Set pc = ActiveWorkbook.PivotCaches.Create(xlDatabase, "Tabla1")

      la palabra Tabla1 por cómo se llame tu Tabla origen...

      Saludos

      Eliminar
  2. Buena tarde Ismael, mi duda seria como que daria en el PivotFiel, para que tome el valor de la celda, si esta cela es cambiante en el valor. Ya que como veo la especifiación es fija ya sea con tu metodo o haciendolo con el grabador de macros.

    ResponderEliminar
    Respuestas
    1. Hola José Luis,
      discúlpame pero no entiendo...?
      te refieres a que el nombre que aparece en la tabla dinámica añadido a algún campo (PivotField) tome el valor de una celda cualquiera?
      Si fuera esto, en todo caso tendrías que asegurarte que ese nombre existe como campo en el origen o fuente de datos...

      No he probado nunca algo así (no tiene para mí mucho sentido), pero entiendo cambiando por una variable el nombre, por ejemplo:

      With pt
      'llevamos al área de filas el campo 'Departamento'
      With .PivotFields(range("A1").value)


      recuerda, en A1 debe poner un nombre que exista!!

      Saludos

      Eliminar
    2. Gracias por responder Ismael, si es algo asi, lo que necesito. Te platico lo que busco hacer. Tengo mi hoja1, en la cual tengo 8 columnas fijas y de ahi hacia la derecha puede variar la cantidad de columnas, desde 1 hasta 25 o mas, y al momento crear la macro para la tabla dinamica tomo las primeras 8 y despues ver si puedo que tome la cantidad de columnas hacia la derecha existentes. Y asi crear las tablas dinamicas, con diferentes tamaños que es lo que necesito. ¿Crees qué se pueda hacer algo asi?.
      Saludos.

      Eliminar
    3. Hola,
      por que no sencillamente conviertes tu origen de datos en una Tabla.. asi tengas las columnas (campos) que tengas se reflejarán en la TD (las tendrás a tu disposición), de igual forma que el número de filas...
      Saludos

      Eliminar
  3. Me sale un error que dice se ha producido un error 5 en tiempo de ejecución
    Argumento o llamada a procedimiento no válida

    ResponderEliminar
    Respuestas
    1. Hola que tal estás?
      un placer saludarte igualmente.

      Habría que saber en qué línea se detuvo el depurador.

      Saludos cordiales

      Eliminar
  4. Hola Ismael: necesito hacerte una consulta.
    La macro funciona perfecto para crear la tabla dinámica.
    Los campos que utilizo son:
    Set campo1 = .AddDataField(.PivotFields("Inscripción"), "Inscriptos", xlSum)
    Set campo2 = .AddDataField(.PivotFields("Asistió"), "Asistencias", xlSum)
    Set campo3 = .AddDataField(.PivotFields("Horas Previstas"), "Hs. Prev.", xlSum)
    Set campo4 = .AddDataField(.PivotFields("Horas"), "Hs. Reales", xlSum)

    Una vez creada la tabla dinámica, necesito agregar campos calculados a la tabla dinámica para calcular porcentajes.
    He intentado con estas sentencias pero me da error:
    ActiveSheet.PivotTables("pt").CalculatedFields.Add "PORCASISTENCIAS", "=Asistió /Inscripción", True
    ActiveSheet.PivotTables("pt").CalculatedFields.Add "PORCHORAS", "=Horas /'Horas Previstas'", True

    Me podrías decir cuál sería el código correcto?
    Muchas gracias.

    ResponderEliminar
    Respuestas
    1. Hola Luciano,
      a priori lo único que veo (con el código parcial que comentas) es que te faltaría añadir una fila de código para identificar donde incluir el campo calculado:
      ActiveSheet.PivotTables("Tpt").PivotFields("PORCASISTENCIAS").Orientation = xlDataField

      Es lo único que podría decirte...

      Saludos!

      Eliminar