Tablas dinámicas y VBA 2ª parte

En un anterior post hemos tratado como se realiza una tabla dinámica. Podemos potenciar el uso de la misma mediante VBA. Para poder seguir los puntos  tratados a continuación sería conveniente descargar el siguiente fichero de datos.

Una vez abierto el fichero desplegamos el editor VBA e insertaremos el módulo que nos va a permitir crear nuestra tabla dinámica.

Comenzamos insertando un módulo al que he llamado CrearTD() en referencia a TablaDinámica. Declaramos una variable para cada uno de los objetos con los que vamos a trabajar:

WSD->nombre de nuestra hoja de cálculo dinámica

PTCache->nombre de la caché de la tabla dinámica

PT->nombre de la tabla dinámica

PRange->rango de datos

FinalRow->fila final

De esta forma cdeclaramos las variables:

Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long

Asignamos una referencia de objeto a la hoja en al que va inserta la tabla dinámica a la que llamaremos Pivot Table.

Set WSD = Worksheets(“Pivot Table”)

Insertaremos a continuación un bucle que nos elimina cualquier otra tabla dinámica que tengamos en nuestra hoja:

For Each PT In WSD.PivotTables
        PT.TableRange2.Clear
    Next PT

Necesitamos definir el área de entrada de datos:

FinalRow = WSD.Cells(65536, 1).End(xlUp).Row ‘(guardo la posición de la última fila con datos)
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, 8) ‘(redimensionamos el área desde la columna 1 a la 8)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange.Address)’ añadimos los datos

Set PT = PTCache.CreatePivotTable(TableDestination:=WSD.Range(“J2″), TableName:=”PivotTable1”) Con esta línea creamos en sí la tabla con destino en la columna J y con mombre PivotTable1.

PT.ManualUpdate = TrueEstablecemos la actualización a manual para evitar errores.

Ahora necesitamos crear los nomres de campos en nuestra tabla. Para ello insertaremos las siguientes líneas:

PT.AddFields RowFields:=Array(“Producto”, “Cliente”), ColumnFields:=”Region”

Ordenamos los datos en el campo Ingresos calculando su total:

With PT.PivotFields(“Ingresos”)
        .Orientation = xlDataField
        .Function = xlSum
        .Position = 1
    End With

Recalculamos todo:

  PT.ManualUpdate = False
    PT.ManualUpdate = True

Dejamos seleccionada la hoja para el siguiente proyecto.

WSD.Select


Puede ver y descargar todo el código junto aquí.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: