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!!).

28 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 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!

    ResponderEliminar
  5. Tengo una pregunta, yo tengo mis tablas dinamicas ya creadas, con su respectivo gráfico. Pero quisiera desde un formulario, poder cambiar los criterios de la tabla dinámica. Por ejemplo mi gráfica tiene tres filtros, Superivisor, incidencia, semana. Los valores de supervisores, pueden variar de 1 a 4, igual los tipos de incidencia de 1 a 4, las semanas, son de 1 a 52 semanas en el año. Sin tener que ir directamente a la tabla dinamica yo modificar por codigo los itmes deseados en cada filtro.

    Otra pregunta mas sencilla creo, como puedo poner una etiqueta en la grafica que me indique cuales son los criterios del filtro.

    ResponderEliminar
    Respuestas
    1. Hola José,
      desde la versión 2010 existen los cuadros de segmentación que hacen precisamente lo que comentas:
      http://excelforo.blogspot.com.es/2011/12/segmentacion-de-datos-en-tablas.html
      Quizá te sirva.. y no emplear así programación
      Saludos

      Eliminar
    2. Cierto, muchas gracias, pero la verdad, es que como el proyecto maneja muchas tablas dinámicas y graficas que parten de ellas, y lo que se requiere es modificarlas prácticamente con el mismo criterio. Estoy revisando tus códigos para generar nuevamente la tabla cada vez que la requieran, especificando los criterios de selección. Lo que no he encontrado es como en una gráfica pueda agregar un cuadro de texto propio y personalizado, no se si me explique..... Existen las etiquetas de los eje, los titutlos, pero si yo quiero agregar una nuevo texto y que forme parte de la gráfica como le agrego ese objeto. Gracias.

      Eliminar
    3. La segmentación de datos te permite desde un mismo cuadro controlar diferentes TD (siempre que partan de un mismo origen).
      Para incorporar un objeto externo (un cuadro de texto, por ejemplo) como parte de la gráfica, tendrías que insertarlo en la hoja, luego situarlo encima del objeto gráfico , seleccionar ambos, y Agruparlos...
      Saludos

      Eliminar
  6. Hola Carlos,
    se puede emplear antes de la línea de inserción de campos (y después de ésta) las instrucciones:
    On Error Resume Next
    tu código
    On Error GoTo 0

    Saludos

    ResponderEliminar
  7. Buenas tardes interesante tu tabla pero que pasa si mi rango de datos es variable? que debo poner en donde pusiste tabla 1?. Es decir mis datos siempre comienzan en "a6", pero el rango es variable.....llego hasta range ("a6").currentregion.select.......ahora lo que quiero es insertar la tabla

    ResponderEliminar
    Respuestas
    1. Hola Manolo,
      precisamente el trabajar con la herramienta Tabla persigue ese objetivo, al ser tabla el tamaño de la tabla se ajusta a la variabilidad tanto de registros como de campos, lo que se transmite a la tabla dinámica...
      Por tanto, el código del post es lo que necesitas, tal cual está.
      Saludos

      Eliminar
  8. Hola, tengo una duda. Cuando vuelvas a correr la macro, la tabla dinamica ya no sera TablaDinamica1, sino 2 y entonces no la leera. como hago?

    ResponderEliminar
    Respuestas
    1. Hola Fiorella,
      si te fijas en el primer código se evita usar el nombre de la tabla dinámica... cargando la caché directamente, por lo que no deberías tener problemas
      Si optas por el código generado por el asistente de grabación, si tendrás que combatir el problema que indicas.
      Saludos

      Eliminar
  9. Hola, me gustaria saber como agregar un campo de filtro a la tabla dinamica

    ResponderEliminar
    Respuestas
    1. Hola,
      para añadir campos al área de filtro:
      With ActiveSheet.PivotTables("TablaDinámica1").PivotFields("Campo")
      .Orientation = xlPageField
      .Position = 1
      End With

      es decir, la orientación debe ser xlPageField

      Saludos

      Eliminar
  10. Hola Ismael
    Muy interesante tus respuestas a las consultas que te realizan, es un gran aporte el que realizas, Felicidades.
    En esta ocasión te deseo consultar que codigo utilizar para poner mi tabla dinamica de forma tabular, quitar los subtotales y repetir las etiquetas.

    Agradeciendo desde ya tu respuesta.

    ResponderEliminar
    Respuestas
    1. Hola Elvin,
      en breve subiré un post exponiendo el caso..

      Saludos y muchas gracias!

      Eliminar
  11. Hola Ismael, me gustaría consultarte una duda: tengo en un libro varias hojas, de las que quisiera hacer una tabla dinámica de cada hoja. ¿Como podría modificar la macro para conseguirlo?

    Muchas gracias de antemano!

    ResponderEliminar
  12. hola ismael;

    Consulta deseo trabajar con tablas dinamicas que se generen a partir de una macro, como puedo realizar los filtros de la TD, por medio de textbox o combobox haciendo referencia a cualquier campo de mi TD dentro de un formulario

    ResponderEliminar
    Respuestas
    1. Hola,
      y por qué no empleas sencillamente los cuadros de segmentación?

      En todo caso, con vba podría ser como se indica en este otro post
      https://excelforo.blogspot.com/2011/11/vba-asociar-los-elementos-de-un.html

      Saludos

      Eliminar
  13. hola como puedo hacer para actualizar una tabla dinamica que dejo oculta y esos datos se muestran en otra hoja a travez de un hiperviculo trato de usar este commando activeSheets.PivotTables("TablaDinámica1").PivotCache.Refresh pero solo funciona si en la misma hoja donde muestra los datos por hipervinculo y no quiero la tabla hay se ve mal ;(

    ResponderEliminar
    Respuestas
    1. Hola,
      en lugar de activesheet indica la hoja donde se encuentre
      worksheets("LA HOJA").PivotTables("TablaDinámica1").PivotCache.Refresh

      saludos

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.