EXPORTAR INFORME DE TABLA DINAMICA A UN LIBRO NUEVO

Una característica de las tablas dinámicas es que una vez confeccionadas, podemos extraer un informe con el detalle de los datos de cada uno de los campos que hemos seleccionado. Cuando lo hacemos, se crea una pestaña nueva que contiene la información requerida.

Pero esto no siempre resulta funcional, cuando hablamos de poca información (pocos campos), no hay problema, generamos el informe y desde la misma pestaña lo pasamos a un libro nuevo. Pero, ¿Qué sucede si hablamos de cientos de campos?, ¿Tendríamos que ir generando y pasando a un libro nuevo cada uno de los datos?, esto sería muy costoso tanto moral como físicamente 😉

Voy a escenificar un ejemplo de lo que pretendo hacer, primero lo expondré cómo se suele hacer manualmente y luego una alternativa mucho más eficaz.

Imaginad que tenemos los datos de población por sexo de todos los municipios de España ordenados por provincia, así:

Ahora realizamos una tabla dinámica, que agrupe la información por cada provincia:

Y ahora si queremos obtener los datos de, por ejemplo, la provincia de Ávila, solo tenemos que hacer doble clic en uno de los campos de totales y obtendremos en una nueva pestaña la información de ÁVILA:

Y si queremos pasar la información de Ávila a un libro nuevo, o bien seleccionamos y pegamos o bien vamos al el menú de la pestaña a “copiar o mover” a un libro nuevo.

Pero, ¿y si resulta que los presidentes de cada diputación provincial nos solicitan el detalle la información de su provincia y tenemos que enviarle la información individual?, pues ya podemos ponernos a generar los 52 ficheros, renombrarlos (por defecto en la tabla dinámica sale el número del libro) e ir pasándolos a un nuevo libro. Esto llevaría unos cuantos minutos ¡u horas!, pero ¿y si mañana lo quieren otra vez? ¿y pasado también?.

Como siempre hay una alternativa con VBA, y es mediante este código que acabo de componer:

Sub Generar_informes()
Dim i As Double
Dim Ini As Double
Dim Fin As Double
'Desactivamos actualización de pantalla
Application.ScreenUpdating = False
Sheets("TABLA").Select
With Sheets("TABLA")
'indicamos la fila siguiente a la primera fila con datos, (Albacete)
Ini = Columns(1).Range("A1").End(xlDown).Row
'Contamos los todas las provincias de la tabla dinámica1, es decir las 52
Fin = .PivotTables(1).PivotFields("PROVINCIA").VisibleItems.Count
'Inciamos un bucle donde por cada provincia generamos informe (ShowDetail)
For i = 1 To Fin
'para mostrar el informe debemos hacer referencia a los campos de valores (nº Hombres o Mujeres),
.Cells(i+Ini, 2).ShowDetail = True
'Nombramos la pestaña con el nombre de la provincia.
ActiveSheet.Name = .Cells(i+Ini, 1).Value
'Seleccionamos la hoja con el detalle de la provincia
ActiveSheet.Select
'Movemos la hoja a un libro nuevo
ActiveSheet.Move
'Guardamos la hoja con el nombre de la provincia correspondiente
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & ActiveSheet.Name
'Cerramos archivo que hemos creado
ActiveWorkbook.Close False
Next i
End With
Application.ScreenUpdating = True
End Sub

Importante en este código:

– Debemos tener en cuenta que el bucle que iniciamos haga referencia a los campos de la tabla dinámica y nunca a los totales. En este ejemplo, hacemos referencia a la provincia (que está en la columna 1).

– Para mostrar los informes de cada uno de los campos, debemos hacer referencia a los totales de la tabla dinámica, ejemplo, el total de Hombres (Suma de Hombres) que está en la columna 2: .Cells(i+Ini, 2).ShowDetail = True.

– Otro tema importante es donde guardamos los informes que vamos a generar, (los libros nuevos). Para este ejemplo, los informes se guardan en la misma carpeta donde tengamos este archivo:

ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & ActiveSheet.Name

Pero se podría indicar cualquier otra ubicación:

ActiveWorkbook.SaveAs Filename:="E:\" & "\" & ActiveSheet.Name

Para darle nombre al nuevo archivo, debemos hacer referencia a la hoja activa que acabamos de crear: ThisWorkbook.Path & "\" & ActiveSheet.Name

Pues bien, una vez ejecutada la macro, debería generar 52 ficheros en la misma carpeta en la que se encuentra este archivo, y este es el resultado:

* Esta macro ha sido probada en las versiones 2010, 2013 y  2016. Su funcionamiento es correcto, si bien, en algunos equipos (dependiendo de los requisitos del sistema), puede experimentar problemas relacionados con el rendimiento del equipo.

Descarga el archivo de ejemplo pulsando en: EXPORTAR INFORME DE TABLA DINAMICA A UN LIBRO NUEVO