Como bien sabemos, las tablas dinámicas de Excel no se actualizan automáticamente al cambiar los datos que las alimentan. Dada esta característica existe el riesgo de publicar informes erróneos si nos olvidamos de pulsar el botón Actualizar en el menú de Herramientas de tabla dinámica
En el pasado
propuse una macro sencilla para asegurarnos que después de actualizar datos en una hoja, la tabla dinámica se actualice. Se trata de un método un tanto contundente, actualiza todo lo que se cruce a su paso: tablas dinámicas, tablas, etc.
Un lector me pide un método más sutil que permite actualizar únicamente las tablas dinámicas y deje todo lo demás intacto. Mi primer propuesta, que puse en un comentario en la nota en cuestión, fue este código
Sub actualizar_Tabla_Dinamica()
Dim strPivotName As String
strPivotName = Application.InputBox(prompt:="Que tabla actualizar?")
On Error GoTo errCancel 'en caso de apretar Cancel
ActiveSheet.PivotTables(strPivotName).PivotCache.Refresh
Exit Sub
errCancel:
Exit Sub
End Sub
En una hoja con varias tablas dinámicas funciona así
El problema con esta macro es que debemos recordar el nombre de las tablas dinámicas. Como pueden ver, el diálogo que se abre tengo que introducir el nombre “TDin1” que identifica a la tabla en cuestión, lo cual puede conducir a errores. Y ya que estamos, unas palabras sobre los nombres de las tablas dinámicas.
Cuando creamos una tabla dinámica, Excel le asigna un nombre por defecto “TablaDinámicaX” donde X es un número de orden. Usando el menú de opciones de las tablas dinámicas podemos cambiar este nombre por algo más significativo (útil en particular cuando escribimos código)
Volviendo a nuestro tema, era obvio que había que escribir un código que permitiera al usuario elegir que tablas o tablas actualizar.
Par lograr esto ya no podemos depender de la función Input, que tiene muchas limitaciones. En su lugar usamos un ListBox que tiene la ventaja de poder realizar selecciones múltiples. Este objeto tenemos que ponerlo en un Userform y crear los eventos para accionarlo y cancelarlo.
El primer paso es crear una Userform donde ponemos los controles List Box y dos CommandButton
Un doble clic en el Userform nos lleva al módulo correspondiente donde ponemos los códigos para manejarlo
Private Sub cbtAceptar_Click()
ufListaTablas.Hide
End Sub
Private Sub cbtCancelar_Click()
Unload Me 'ufListaTablas
End Sub
Ahora, en un módulo común ponemos el código de la macro
Sub actualizar_Tabla_Dinamica()
Dim pt As PivotTable
Dim lbItemsCount As Integer
Dim iX As Integer
'poner los nombres de las tabla en un List Box
With ufListaTablas.lbPivots
.Clear
For Each pt In ActiveSheet.PivotTables
.AddItem pt.Name
Next pt
lbItemsCount = .ListCount
End With
'abrir el List Box para elegir la tabla a actualizar
ufListaTablas.Show
On Error GoTo errCancel 'en caso de apretar Cancel
With ufListaTablas.lbPivots
For iX = 0 To lbItemsCount - 1
If .Selected(iX) = True Then
ActiveSheet.PivotTables(.List(iX)).PivotCache.Refresh
End If
Next iX
End With
Unload ufListaTablas
Exit Sub
errCancel:
Exit Sub
End Sub
El ListBox muestra las tablas dinámicas de la hoja activa y nos permite elegir cuáles actualizar.
Para que la macro pueda usarse en cualquier cuaderno abierto sugiero guardarla en el cuaderno Personal y crear una icono en la barra de herramientas de acceso rápido.
El cuaderno con el ejemplo
puede descargarse aquí.
