Excel Avanzado

Macros, Vba en Excel y muchos ejemplos de nuestro Curso de Excel Avanzado

Excel Avanzado

Crear una lista de Tablas Dinámicas de un libro

| 5 comentarios

Cuando nuestro libro en Excel contiene varias tablas dinámicas, resulta útil crear un cuadro resumen con la información básica de las mismas. Este cuadro puede contener detalles como la ubicación de cada tabla, el rango de datos que está usando, etc.

Código empleado

Sub Macro1()
'Paso 1: Declarar las variables
Dim ws As Worksheet
Dim pt As PivotTable
Dim MyCell As Range

'Paso 2: Agregar una nueva hoja con las cabeceras de columnas
Worksheets.Add
Range("A1:F1") = Array("Nombre_Tabla", "Hoja", "Ubicación_Tabla", "Cache Index", "Ubicación_Data", "Contador de Filas")

'Paso 3: Colocar el cursor en Celda A2
Set MyCell = ActiveSheet.Range("A2")

'Step 4: Bucle a través de cada hoja en el libro
For Each ws In Worksheets

'Step 5: Bucle a través de cada tabla dinámica
For Each pt In ws.PivotTables
MyCell.Offset(0, 0) = pt.Name
MyCell.Offset(0, 1) = pt.Parent.Name
MyCell.Offset(0, 2) = pt.TableRange2.Address
MyCell.Offset(0, 3) = pt.CacheIndex
MyCell.Offset(0, 4) = Application.ConvertFormula _
(pt.PivotCache.SourceData, xlR1C1, xlA1)
MyCell.Offset(0, 5) = pt.PivotCache.RecordCount

'Step 6: Movee Cursor y continuar con el llenado de la tabla resumen
Set MyCell = MyCell.Offset(1, 0)

'Step 7: Seguir trabajando a través de las tablas dinámicas y hojas
Next pt
Next ws

'Step 8: Ajustar tamaño de columnas
ActiveSheet.Cells.EntireColumn.AutoFit
End Sub

 

Funcionamiento

Macro en Excel: Tabla Resumen

Tabla

(Para mayor visualización de la imagen, hacer click en la misma)

 

Cuando creamos una tabla dinámica, esta posee propiedades como nombre, ubicación, rango de datos, etc. Debido a que las tablas se encuentran dentro de una hoja de cálculo, primero debemos recorre las hojas del libro y luego, las tablas de cada hoja.

En el primer paso, declaramos el objeto llamado ws.  Este crea un contenedor de memoria para cada hoja a través de la cual hacemos el bucle. Luego, declaramos el objeto pt que nos sirve para las tablas dinámicas.

En el segundo paso, se crea una nueva hoja y agrega los encabezados de nuestra tabla resumen.  Posteriormente, en el paso 3, colocamos el cursor en la primera celda debajo de las cabeceras, es decir, la celda A2. Este es el punto de anclaje desde donde nos moveremos. El comando utilizado para ello es Offset, el cual permite mover el cursor x número de filas y x número de columnas desde nuestro punto.

En el punto 4, comienza el bucle a través de todas las hojas en el libro de Excel. Mientras que en el punto 5, se realiza el bucle a través de todas las tablas dinámicas en cada hoja. Para cada tabla encontrada,  la macro extrae las propiedades requeridas y las coloca en nuestra tabla resumen.  En este caso, hemos creado una tabla con seis propiedades:  "Nombre_Tabla", "Hoja", "Ubicación_Tabla", "Cache Index", "Ubicación_Data" y  "Contador de Filas".

En Nombre_Tabla, la macro coloca el nombre de cada tabla dinámica; en Hoja, se escribe el nombre de la hoja en la cual se ubica cada tabla dinámica, la tercera columna describe la ubicación de cada tabla dentro de su hoja respectiva; mientras que, la propiedad Cache Index devuelve el número de índice de la caché de pivote  (es un contenedor de memoria que almacena todos los datos de una tabla dinámica). Cuando se crea una nueva tabla dinámica, Excel toma una instantánea de los datos de origen y crea una caché de pivote. Cada vez que actualiza una tabla dinámica, Excel se remonta a los datos de origen y toma otra instantánea.

En Ubicación_Data, la macro escribe la ubicación de la data utilizada para crear cada tabla dinámica y finalmente, el Contador de Filas nos dice el número de filas del origen de los datos.

Cada vez que la macro se encuentra con una nueva tabla dinámica, se mueve el cursor Mycell abajo de una fila, comenzando con una nueva fila para cada tabla dinámica. El paso 7 regresa al inicio para recorrer todas las tablas dinámicas y todas las hojas de trabajo. Después de que se han evaluado todas las tablas dinámicas, pasamos a la siguiente hoja. Después de que todas las hojas han sido evaluadas, la macro avanza al siguiente paso.  Con el paso 8, se culmina la macro, dándole el formato adecuado a nuestra tabla resumen.

Por: Manuel Talledo Carrasco.

5 comentarios

  1. Muchísimas gracias, me sirvió bastante.

  2. Muy util para los que utilizamos asiduamente tablas dinamicas en libros de trabajo

  3. Me sirvió, gracias.

  4. Excelente aporte. Muchas gracias

  5. Excelente

Deja una respuesta

Los campos requeridos estan marcados con *.