EXPORTAR INFORME DE TABLA DINÁMICA EN LA MISMA HOJA

Hola todos 😉

Hoy me gustaría ampliar parte de un post anterior: EXPORTAR INFORME DE TABLA DINAMICA A UN LIBRO NUEVO en el que mediante una macro generábamos automáticamente informes de tabla dinámica y exportábamos cada uno de ellos a un nuevo libro.

Hace unos días un lector quería saber qué modificaciones tenía que hacer en la macro para que esos informes no se generasen en un libro nuevo, sino que se generasen en la misma hoja, uno por pestaña.

Lo cierto, es que la modificación no es compleja, solo hemos de eliminar de la macro anterior toda referencia a “nuevo libro” y dejar que los informes se generen automáticamente en la hoja.

Voy a utilizar un ejemplo nuevo para ilustrar el ejercicio, en este caso, volvemos a los datos por provincias y por número de personas en determinados años. Esta sería la información principal, que la tenemos en una hoja denominada “DATOS”:

El siguiente paso es insertar una tabla dinámica y en la pestaña Diseño aplicar “mostrar en forma de esquema”, de esta forma aparecerá el título del primer campo seleccionado, en nuestro caso: «Provincia»

Una vez que tenemos la tabla creada, procedemos escribir nuestro código:

Sub GENERAR_INFORMES_TABLA()
Dim i As Integer
Dim Ini As Integer
Dim Fin As Integer
'Desactivamos actualización de pantalla
Application.ScreenUpdating = False
Sheets("TABLA").Select
With Sheets("TABLA")
'indicamos la fila siguiente a la primera fila con datos: 01 Araba/Álava
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 al campo de valor de la tabla
.Cells(i + Ini, 2).ShowDetail = True
'Nombramos la pestaña con el nombre de la provincia.
ActiveSheet.Name = Application.WorksheetFunction.Substitute((.Cells(i + Ini, 1).Value), "/", "_")
Next i
End With
Application.ScreenUpdating = True
End Sub

Lo que vamos a conseguir con esta macro es generar tantos informes como ítems tengamos en la tabla dinámica y los colocaremos en una pestaña cada uno. Dado que estamos nombrando las pestañas de las hojas, debemos tener en cuenta:

– El nombre de la hoja no puede superar los 31 caracteres.
– No ha de contener los siguientes caracteres: \  / ¿ ? * [  ]
– No puede quedar el nombre en blanco.

Seguro que os habéis fijado que en nuestros datos tenemos uno de esos símbolos «/»: por ejemplo en 01 Araba/Álava. Para solucionar este problema sin tener que modificar los datos, realizaremos el cambio en el mismo momento que nombramos la hoja, simplemente sustituyendo “/” por “_” (por ejemplo). Esta es la línea del código en la que hacemos este reemplazo:

ActiveSheet.Name = Application.WorksheetFunction.Substitute((.Cells(i + Ini, 1).Value), "/", "_")

Por supuesto, también podríamos utilizar la función Mid () si necesitásemos extraer los 31 caracteres de la hoja y la función Trim() para eliminar los espacios en blanco.

Una vez aplicada la macro, el resultado sería el siguiente:

Y ya tenemos una pestaña por provincia con el informe correspondiente.

Y estos ha sido todo, espero que os sea de utilidad:

Descarga el archivo de ejemplo pulsando en: EXPORTAR INFORME DE TABLA DINÁMICA EN LA MISMA HOJA