sábado, 6 de marzo de 2010

Actualización automática de una Tabla Dinámica

Descargar el fichero: tdauto.xlsm

Automatizar la actualización de una Tabla Dinámica es posible creando una pequeña macro, que se lanza cuando se produce un evento. Esto es lo que se denomina programación por eventos. En este caso el evento consiste en actualizar alguna cifra de un campo concreto de la base de datos. Si se modifica cualquier otro dato de la base de datos la tabla dinámica no se actualiza, aunque sería fácil programarlo para que se actualizara al cambiar cualquier dato.


Elegimos la columna D que corresponde en la base de datos al campo "VENTAS".  Cualquier modificación en las ventas de alguno de los comerciales automáticamente quedará reflejada en la Tabla Dinámica.

El código utilizado ha sido el siguiente.

Código:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then
   ActiveSheet.PivotTables("Tabla dinámica1").PivotCache.Refresh
End If
End Sub


Si lo que deseas es que la Tabla Dinámica se actualice ante cualquier variación en el rango B4:E39, podrías indicárselo a Excel de esta forma.
Código:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B5:E39")) Is Nothing Then
   ActiveSheet.PivotTables("Tabla dinámica1").PivotCache.Refresh
End If
End Sub


Existe otra alternativa para cuando el Target es un rango. Se trata de indicar las filas y columnas que deseamos que se controlen.
Código:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column >= 2 And Target.Column <= 5 And _
Target.Row >= 5 And Target.Row <= 39 Then
   ActiveSheet.PivotTables("Tabla dinámica1").PivotCache.Refresh
End If
Cuando el Target es una única celda el condicional se puede escribir así:

If Target.Address="$A$1" Then

Al escribir la celda incluir los dólares y escribir en mayúsculas.

12 comentarios:

  1. Hola buen día y gracias. Ese es un excelente ejemplo de como usar una macro. Muy ilustrativo, muchas gracias por el archivo. Adelante.

    ResponderEliminar
  2. Hola, ¡¡¡excelente!!! tu aporte.
    Didactico y muy bueno.
    Gracias

    ResponderEliminar
  3. Excelente, Adolfo!!
    Consulta:
    ¿como debería ser la macro en caso de que el rango que variará, se encuantre en otro libro?
    Es decir, la Tabla Dinámica está en un libro y el Rango de Datos que alimenta a la Tabla está en otro libro.

    Gracias de antemano.

    ResponderEliminar
  4. genial resolvió mi problema gracias !

    ResponderEliminar
  5. Hola, muy buena informacion, quisiera saber como programar este modelo de macro pero que la actualizacion de la informacion se haga automaticamente cada 5 minutos independientemente de algun cambion en los datos de las tablas. Gracias

    ResponderEliminar
  6. Mucha gracias Adolfo,

    Muy útil.

    Saludos,
    gab

    ResponderEliminar
  7. buen dia, es posible programar una acutalizacion de tabla dianmica, pero desde una tarea de windows?

    ResponderEliminar
  8. Hola ARY.

    Creo que si es posible. Puedes probarlo utilizando una macro de auto arranque. Existe una macro muy especial que se llama:

    sub auto_open()

    end sub

    Es una macro que se ejecuta justo en el momento en el que se abre la aplicación Excel.

    Por tanto, la secuencia sería la siguiente:

    1. La tarea de Windows abre Excel
    2. Excel ejecuta automáticamente lo que diga la macro auto_open
    3. La macro auto_open lanza la actualización de la tabla dinámica

    Un saludo.

    ResponderEliminar
  9. Hola, existe alguna manera de tener un paso paso por medio de una macro de lo que se va actualizando al presionar actualizar todo ?

    Gracias por su respuesta

    ResponderEliminar
  10. Como hacer que la la tabla se actualice si esta en otra Hoja del libro

    ResponderEliminar
  11. buenas, esta macro solo funciona para cuando se digitan datos, pero cuando los datos se actualizan de forma automática??

    ResponderEliminar
  12. He hecho la macro indicada debajo

    Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.PivotTables("Resum").PivotCache.Refresh
    End Sub


    pero al modificar una celda de la tabla de datos me da el error:

    Se ha producido el error '-2147417848 (80010108)' en tiempo de ejecucion.
    Error en el metodo 'Refresh' de objeto 'PivotCache'

    Alguna idea?

    ResponderEliminar