REACOMODANDO UNA TABLA DINÁMICA CON MACROS

Hoy vamos a ayudar a tu jefe el que no sabe como usar una tabla dinámica para que pueda mover nuestro informe de manera que mejor le convenga (y no te este interrumpiendo cada dos minutos jeje).

Los métodos de tabla dinámica en VBA exigen fundamentalmente dos parámetros, el nombre de la tabla y el nombre del campo. El primero podemos obviarlo como un valor constante (casi nadie le cambia los nombres a la tabla dinámica) y el segundo lo conseguiremos con variables de arraigo.

Vamos primero por lo simple, crearemos una lista de valores únicos en una hoja separada de la data y de la pivote para poder acceder a ella de manera más rápida y sin que se modifique por el usuario final (tu jefe el que todo quiere borrar). Esta lista podemos tomarla directamente de los encabezados o, como en este caso, con una formula DESREF sobre la cual haremos que los valores sean variables según la data se modifique.

Para el primer valor tenemos entonces

=DESREF(DATA!$B$2,0,FILA($A$1)-1)

Crecemos en dificultad y ahora añadiremos un botón de cuadro de lista, sobre el cual depositaremos los valores de los encabezados. Quedando así sus parámetros.

Esta todo listo para la macro, misma que puedes tomar de la parte de abajo.

Sub cambiar_pivot()

'

' Macro1 Macro

'
Application.ScreenUpdating = False

'

' definimos variables

Dim campo() As String

Dim i, j, k As Integer

Dim rng As Long

' vamos primero por los valores de la lista

    Worksheets("calc").Activate

        rng = Application.WorksheetFunction.CountA(Range("A2:A1000000")) - 1

' redefinimos el largo del arraigo

        ReDim campo(rng)

        i = 2

        j = 0

' esta parte agrega los valores al arraigo

            Do Until Cells(i, 1).Value = ""

                campo(j) = Cells(i, 1).Value

                i = i + 1

                j = j + 1

            Loop

'regresamos a la hoja pivot para modificarla

    Worksheets("pivot").Activate

' quitamos los subtotales existentes

        For i = 0 To rng

                ActiveSheet.PivotTables("TablaDinámica1").PivotFields(campo(i)).Subtotals = _

                Array(False, False, False, False, False, False, False, False, False, False, False, False)

        Next i

' escojemos el valor a acomodar primero

    k = Range("C2").Value - 1

            With ActiveSheet.PivotTables("TablaDinámica1").PivotFields(campo(k))

                .Orientation = xlRowField

                .Position = 1

            End With

' el valor escogido le activamos los sobtotales

    ActiveSheet.PivotTables("TablaDinámica1").PivotFields(campo(k)).Subtotals = _

        Array(True, False, False, False, False, False, False, False, False, False, False, False)

Application.ScreenUpdating = True

End Sub

Ahora, con la macro añadida a un módulo, solo falta designarla al botón para que, con cada cambio, realice las funciones debidas.

¡listo! Cada que apretemos una opción de la lista, la macro hará las funciones del menú de configuración de campos, dejándonos mucho tiempo para que nuestro usuario jefe juegue con la tabla a como le acomode. Puedes descargar el libro aquí para ver cómo funciona e insertar tu propia tabla dinámica.