2014-03-01

Cambiar origen de datos de múltiples tablas dinámicas con VBA

Una tarea bastante frecuente trabajando con tablas dinámicas es cambiar el origen de datos. La acción es bastante rápida para una sola tabla dinámica. En el caso de múltiples tablas dinámicas, el procedimiento se vuelve muy pesado. No es posible cambiar manualmente el origen de datos para varias tablas dinámicas simultáneamente. Sin embargo, podemos sortear esta limitación programáticamente:

1. Definimos un nombre (NuevoOrigen) para el rango de celdas que será el nuevo origen de datos.
2. Copiamos el siguiente código en un módulo. Nos aseguramos de que SourceData es igual al nombre definido.
Option Explicit
Sub Cambiar_Origen_Tbls_Dinamicas()
Dim n As Integer
Dim i As Integer
Dim pt As PivotTable
n = ActiveWorkbook.Worksheets.Count
    For i = 1 To n
        For Each pt In ActiveWorkbook.Worksheets(i).PivotTables
            pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create _
            (SourceType:=xlDatabase, SourceData:="NuevoOrigen")
        Next pt
    Next i
End Sub
En lugar de un nombre, podemos indicar un rango de celdas, p. ej.: SourceData:="Hoja1!$A$1:$M$1000"

 3. Ejecutamos el código anterior. El origen de datos habrá cambiado para todas las tablas dinámicas del libro.

19 comentarios:

  1. Excelente aporte gracias

    ResponderEliminar
  2. Hola gracias, pregunta: Cómo puedo activar un campo para todas las tablas dinámicas que tienen en común?. Usando la lógica que usaste en este ejemplo?.

    ResponderEliminar
    Respuestas
    1. Hola Javier, graciar por el comentario. No queda clara tu pregunta. Entiendo que quizá te refieres a filtrar todas las tablas dinámicas. Quizá le dedique una entrada próximamente. Te recomiendo, si no encuentras una respuesta en otros blogs, plantear tu pregunta de una manera más clara y específica en algún foro.
      Saludos,

      Eliminar
    2. Imagino que ya lo solucionarías, sino, tu solución es una segmentación de datos.

      Eliminar
    3. Saludos Javier,
      Tengo el mismo problema, encontraste la solucion?

      Eliminar
  3. Que genialidad este codigo. Gracias por compartirlo! me acaba de ahorrar muchas horas de trabajo.

    ResponderEliminar
  4. Perdona,.... ¿cómo lo ejecuto? no tengo idea de programación en excel

    ResponderEliminar
    Respuestas
    1. https://nubededatos.blogspot.com.es/2017/10/comprobar-si-una-celda-contiene-un.html

      Eliminar
  5. Years spending endless hours in this.... jajajaja, thanks a lot

    ResponderEliminar
  6. excelente ayuda, tengo una pregunta, tengo varias tablas dinamica con su respectiva segmentacion de datos, al momento de ejecutar este codigo para cambiar el origen de datos, todos los segmentadores que tenia, los voy a conectar a las tablas dinamicas, pero solo me esta reconociento una sola, las otras no....

    Espero tu valiosa ayuda, de antemano muchas gracias...

    ResponderEliminar
  7. Buen día, podrían apoyarme en lo siguiente, .... cada semana mediante una macro se toma una base central de estado de proyectos y servicios por cada lider, se concentra y se dispersa en distintos archivos por administración regional (que se debe enviar en forma individual) y en varias pestañas u hojas, ... las pestañas llevan información por cierta clasificación cada una (4 o 5 pestañas) y una pestañas lleva unas tablas dinámicas totalizando por distintos pivotes.
    el asunto es que el apuntador de "origen de datos" deja como apuntador a la base central, y por macro requerimos que apunte a las pestañas de cada nuevo archivo creado.
    Entonces tenemos que actualizar los apuntadores manualmente para que tomen la información del propio libro una vez que la macro proceso y lo creo, y la intención es actualizar esas referencias en forma automática vía la propia macro.
    alguien tiene una experiencia similar que me pueda orientar??

    ResponderEliminar
  8. Me sale un error de tiempo "438" no ejecuta

    ResponderEliminar
  9. Hola! Alguien sabe cómo puede aplicarse esta macro sólo a 1 hoja?

    Muchas gracias!

    ResponderEliminar
  10. Hola, no logro ejecutar esta macro. Me arroja un error "Run-time error '5': Invalid procedure call or argument"

    Sub SourceMarchFY21()
    Dim n As Integer
    Dim i As Integer
    Dim pt As PivotTable

    For Each pt In ActiveWorkbook.Worksheets("Pivot DASH").PivotTables
    pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create _
    (SourceType:=xlDatabase, SourceData:="MarchFY21")
    Next

    End Sub

    Muchas gracias!!!

    ResponderEliminar

Nube de datos