domingo, 20 de mayo de 2012

Mantener siempre actualizada una Tabla Dinámica en Excel



Todos los usuarios que utilicen Tablas Dinámicas, saben que estas deben ser actualizadas para que muestren los posibles cambios que hubiere en los datos de origen.



De forma básica, esta actualización se puede hacer de dos formas:

  • Manual:  mediante el botón actualizar que vemos en la ficha Opciones (sólo disponible cuando el cursor está situado en la Tabla Dinámica).
  • Automática: activando la casilla "Actualizar al abrir el archivo" que hay en la pestaña "Datos" del cuadro de diálogo "Opciones" que podemos activar desde la ficha "Opciones" (como hemos dicho antes, sólo disponible cuando el cursor está situado en la Tabla Dinámica".



Vamos, en este artículo, a proponer una tercera alternativa de actualización (automática) mediante una pequeña rutina de código que debemos introducir en el evento "open" de la hoja donde está alojada la Tabla Dinámica.

¿Cómo se hace esto?

Paso 1
  • Presionamos Alt + F11, o bien en la Ficha Programador, hacemos click en el botón "Visual Basic". Se abre el Editor de Visual Basic.
  • En el explorador de proyectos, seleccionamos la hoja donde está la Tabla Dinámica que queremos actualizar de forma automática:
  • Con el botón derecho, hacemos click en dicha hoja y seleccionamos "Ver código".
Paso 2

...hecho lo anterior, se nos abre una ventana a la derecha, donde debemos escribir:

Private Sub Worksheet_Activate()
ActiveSheet.PivotTables("
NombreTablaDinámica").PivotCache.Refresh
End Sub




...donde vemos que "NombreTablaDinñamica" debe ser el nombre de nuesrta Tabla Dinámica, el cual podemos previamente definir y ver en el campo "Nombre de Tabla Dinámica" que hay en la ficha "Opciones" de las Herramientas de Tablas Dinámicas (recordamos una vez más que debemos estar situados en la Tabla Dinámica para obtener esta ficha):



El efecto que hemos conseguido es que, cada vez que activamos la hoja donde hemos alojado el código, se actualiza la Tabla Dinámica.

Debemos recordar dos cuestiones:
  • Cada vez que actualizamos....¡¡se actualizan todas las Tablas Dinámicas del libro que tengan exactamente el mismo origen de datos!!.
  • No es necesario que la hoja que cause la actualización sea donde está la Tabla Dinámica.
Paso 3

No hay que olvidar que cuando introducimos código en un archivo de Excel, debemos "Guardarlo como..." un archivo de tipo .xlsm (Habilitado para macros). Por lo tanto, debemos ir a la pestaña Archivo (si estamos en Excel 2010, 2013 o 2016) o el botón de Office (si estamos en Excel 2007) y después elegir el comando "Guardar como..." y después el tipo de archivo que hemos comentado anteriormente.




... Aceptar.


  
José Manuel Pomares Medrano


31 comentarios:

  1. muy buen tip, congratulatios!

    ResponderEliminar
  2. Lo maximo y yo matandome donde conseguir codigo para Vb6 y la solución fue configurando el excel que tenia como plantilla y luego abrirlo en el programa y luego salvarlo con otro nombre. Gracias.

    ResponderEliminar
  3. Excelente aporte.
    Doull Orellana desde Honduras.

    He estado buscado esta solución por todo el internet, y la he encontrado gracias a su ayuda y conocimientos.

    Que tenga buen dia.

    ResponderEliminar
  4. Muy buen aporte! La macro funciona de 10.

    ResponderEliminar
  5. Me funciona siempre y cuando no cierre el archivo, al abrirlo y modificar datos no se me actualizan automaticamente tengo que volver a escribir el codigo. Como puedo hacer para que abra o cierre el archivo este siempre me funcione automaticamente?
    Gracias por la ayuda

    ResponderEliminar
    Respuestas
    1. Disculpad por mi explicación. Mi error fue considerar que sabíais algo que no tenéis por qué saber... El motivo del malfuncionamiento es que el archivo debe ser guardado posteriormente a la introducción del código como un "Archivo de Excel habilitado para maros" y que tiene la extensión .xlsm (podéis ver el artículo actualizado).

      Eliminar
  6. Buenas tardes. A mi me sucede lo mismo que Marisa Cruz, si cierro el archivo no se actuliza la tabla cuando hago cambios en los datos.

    ResponderEliminar
    Respuestas
    1. Disculpad por mi explicación. Mi error fue considerar que sabíais algo que no tenéis por qué saber... El motivo del malfuncionamiento es que el archivo debe ser guardado posteriormente a la introducción del código como un "Archivo de Excel habilitado para maros" y que tiene la extensión .xlsm (podéis ver el artículo actualizado).

      Eliminar
  7. Me pasa lo mismo que a Andrea y Marisa

    ResponderEliminar
    Respuestas
    1. Disculpad por mi explicación. Mi error fue considerar que sabíais algo que no tenéis por qué saber... El motivo del malfuncionamiento es que el archivo debe ser guardado posteriormente a la introducción del código como un "Archivo de Excel habilitado para maros" y que tiene la extensión .xlsm (podéis ver el artículo actualizado).

      Eliminar
  8. Hola, has indicado que "No es necesario que la hoja que cause la actualización sea donde está la Tabla Dinámica" ¿cómo puedo hacer que la tabla se actualice en automático sin entrar a la hoja que incluye el código? hasta ahora me veo forzado a dar clic sobre la hoja contenedora para que los datos se actualicen. ¿Existe forma de llamar esta función a través de un botón puesto en una celda?

    Gracias por tu ayuda

    ResponderEliminar
    Respuestas
    1. Por supuesto. Hay varias formas, según necesidades:

      Si generas un botón (ya sea con una figura o un botón de formulario desde la ficha Programador) puedes agregarle una macro que contenga ese mismo código (puesto en un módulo estandar) con el botón derecho del ratón y eligiendo "Asignar macro...".
      También puedes hacer que la TD se actualice mediante ese código si lo pones en un evento que se produce cada vez que se activa CUALQUIER HOJA del libro. Este evento del que te hablo pertenece a ThisWorkbook y se llama SheetActivate (por lo tanto,e l código debes ponerlo dentro de él).
      Hay todavía más formas, pero espero que alguna de estas te sirva.
      Espero haberte ayudado. Saludos.

      Eliminar
  9. Muchas gracias!!!! Excelente!!!

    ResponderEliminar
  10. Muchísimas gracias por el aporte, es muy útil!! Pero me surge un problema, yo tengo tres tablas dinámicas diferentes y me da un error, ¿se pueden incluir las tres en el mismo comando? He probado a copiar y pegar la segunda línea cambiando el nombre de la tabla dinámica para que aparezcan las tres, pero me da otro error. Muchas gracias.

    ResponderEliminar
  11. Muchísimas gracias por el aporte, es muy útil!! Pero me surge un problema, yo tengo tres tablas dinámicas diferentes y me da un error, ¿se pueden incluir las tres en el mismo comando? He probado a copiar y pegar la segunda línea cambiando el nombre de la tabla dinámica para que aparezcan las tres, pero me da otro error. Muchas gracias.

    ResponderEliminar
    Respuestas
    1. Puedes actualizar todas las TDs del libro solo con la línea:
      ActiveWorkbook.RefreshAll
      Sustitúyela por las otras.
      Saludos.

      Eliminar
    2. Muchas gracias de nuevo, funciona perfectamente!

      Eliminar
  12. Gracias por el aporte, estaba buscando un comentario como este, quiero actualizar todas las TDs del libro. Te refieres a que sustituyamos todo esto:
    Private Sub Worksheet_Activate()
    ActiveSheet.PivotTables("NombreTablaDinámica").PivotCache.Refresh
    End Sub

    por esto:
    ActiveWorkbook.RefreshAll

    Si esto es correcto, a mi no me funciona.

    ResponderEliminar
  13. Ya entendí, sería:

    Private Sub Worksheet_Activate()
    ActiveSheet.PivotTables("NombreTablaDinámica").PivotCache.Refresh
    ActiveWorkbook.RefreshAll
    End Sub

    ResponderEliminar
    Respuestas
    1. Puedes hacer que la TD se actualice mediante el código 'ActiveWorkbook.RefreshAll' si lo pones en un evento que se produce cada vez que se activa CUALQUIER HOJA del libro. Este evento del que te hablo pertenece a ThisWorkbook y se llama SheetActivate (por lo tanto,e l código debes ponerlo dentro de él)
      Si el código lo pones dentro de una hoja concreta, funcionará y todas las TDs se actualizarán, pero solo cuando se activa esa hoja.
      Saludos.

      Eliminar
  14. Pueden pegar el código en un botón y al darle click se actualiza, no será necesario cambiar entre hojas.

    ResponderEliminar
  15. Buen dia, tengo una tabla dinamica que utiliza 11 hojas del archivo, y todo muy bien. cierro el archivo se lo envio a otra persona y al abrirlo y querer actualizar la tabla pues modifico informacion, la tabla se borra. sabes a que se debe?

    Saludos

    ResponderEliminar
  16. Es posible que la tabla intente actualizar información de una tabla que no encuentra y, por lo tanto, la estructura de la tabla dinámica desaparece.
    Debe asegurarse de que la tabla conoce su origen de datos y este existe en el ordenador donde se ejecuta.

    ResponderEliminar
  17. Hola, sabes que por mi parte funciona bien, cumple. Pero necesito bloquear la hoja donde esta la tabla dinamica, ya que no quiero que sea modificada, solo necesito que ingresen los datos a una hoja x del mismo libro y que la tabla se actualice. Al bloquear la hoja me bloquea la macro de actualización y ahí quedo. muchas gracias! espero me puedas ayudar.

    ResponderEliminar
  18. hola,
    el codigo funciona perfectamente, pero en mi caso quisiera actualizar una tabla dinamica automaticamente que tiene el origen de datos en otro libro.
    pordria ayudarme

    ResponderEliminar
    Respuestas
    1. Hola...
      No es ningún condicionante para que el código actualice una tabla dinámica, independientemente de dónde esté el origen de datos. Además, lo acabo de comprobar.
      Saludos. :)

      Eliminar
  19. Hola, tengo una hoja de datos, una hoja con varias tablas dinamicas y un dashboard relaxionado con las tablas.
    He conseguido que se actualicen todas las tablas cuando modificas datos, pero si añado filas no se me actualiza el origen de los datos en las tablas dinámicas.
    Puedes echarme un cable?
    Gracias

    ResponderEliminar
    Respuestas
    1. El motivo es que las tablas de origen de datos no tienen el formato de TABLAS DE EXCEL.

      Debes:
      1) Dar formato de TABLA DE EXCEL a los rangos de origen
      2) Seleccionar cualquier celda de cada tabla dinámica y, desde la ficha "Análisis de tabla dinámica", seleccionar "Cambiar el origen de datos de la tabla dinámica". Ahí tienes que seleccionar el nuevo rango que será una tabla (no una referencia de celdas).

      El formato de tabla de Excel trata el conjunto de datos como tal y no como filas separadas

      Saludos

      Eliminar