ACTUALIZAR TODAS LAS TABLAS DINÁMICAS DE UN LIBRO

Hola a todos!:

Hace unos días un lector me preguntaba acerca de cómo podía actualizar todas las tablas dinámicas que tenía en su libro de trabajo. Para poder hacer esto es necesario realizar una sencilla macro que recorra todas las hojas de nuestro libro y en el momento que detecte una tabla dinámica, la actualice o la refresque.

La macro que vamos a utilizar es la siguiente:

Sub Actualiza_Todas_Tabla()
'Declaramos variables
Dim nHojas As Worksheet
Dim tDinamica As PivotTable
'Recorremos todas las hojas
For Each nHojas In ThisWorkbook.Worksheets
'Recorremos todas las tablas de cada hoja
For Each tDinamica In nHojas.PivotTables
'Refrescamos
tDinamica.RefreshTable
Next tDinamica
Next
End Sub

Como podéis comprobar en los comentarios de la macro se realiza la tarea requerida.

A continuación, este lector me solicitaba lo mismo para condicionando solo algunas de las tablas dinámicas para ser actualizadas, por ejemplo que solo actualice la tabla dinámica 1:

Sub Actualiza_Tabla()
'Declaramos variables
Dim nHojas As Worksheet
Dim tDinamica As PivotTable
'Recorremos todas las hojas
For Each nHojas In ThisWorkbook.Worksheets
'Recorremos todas las tablas de cada hoja
For Each tDinamica In nHojas.PivotTables
'Si la tabla se llama TablaDinámica1 entonces refrescamos esa tabla.
If tDinamica.Name = "TablaDinámica1" Then tDinamica.RefreshTable
Next tDinamica
Next
End Sub

Y eso es todo, ha sido un post muy breve, pero seguro que os resulta de utilidad en vuestros proyectos.

🙂

Anuncios

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”:

EXPORTAR INFORME DE TABLA DINAMICA EN LA MISMA HOJA.jpg

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”

EXPORTAR INFORME DE TABLA DINAMICA EN LA MISMA HOJA1

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:

EXPORTAR INFORME DE TABLA DINAMICA EN LA MISMA HOJA2.jpg

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

 

 

TRANSPONER DATOS PERSONALIZADOS EN EXCEL

Hace unos días recibí una consulta en la que se me planteaba la problemática de realizar una transposición de datos, es decir, colocar de forma horizontal una serie de datos que previamente estaban ordenados de forma vertical.

Pues bien, existen múltiples forma de hacerlo, se puede hacer con fórmulas, se puede hacer con código y se puede hacer con tablas dinámicas … o utilizando código en la propia tabla dinámica. De esta última forma estamos aprovechando toda la potencia de la tabla dinámica y la versatilidad del código para realizar trabajos personalizados.

Para ilustrar este caso práctico voy a poner el ejemplo de una comunidad de propietarios, donde en una hoja denominada (DATOS) tenemos la siguiente información: ID, NOMBRE, FECHA e IMPORTE distribuida en cuatro columnas. En la columna “IMPORTE” está la cuota que han pagado cada mes y en la FECHA el histórico desde enero de 2014 hasta junio 2015.

TRANSPONER DATOS PERSONALIZADOS_1

Lo que queremos conseguir es mostrar la información en registros únicos en cada fila (ID y NOMBRE) y una columna por cada mes mostrando el importe de cada una de las mensualidades por mes. Exactamente esto:

TRANSPONER DATOS PERSONALIZADOS_2

Pues bien, esta información la podemos obtener directamente realizando una tabla dinámica, indicando para las columnas los campos ID y NOMBRE y para la información en horizontal la FECHA. Con la vista de tabla dinámica los datos son estos, idénticos a la imagen anterior, solo que los de arriba son valores y los de abajo son una tabla dinámica:

TRANSPONER DATOS PERSONALIZADOS_3

Por lo tanto, sabemos que podemos realizar la tarea de transponer con una simple tabla dinámica, indicando la información correcta y ocultado o eliminando totales y subtotales de fila y columna. Genial 🙂

Pero … (siempre hay un pero) resulta que lo que en realidad queremos es que se genere esa información desde una tabla con unos datos que van cambiando periódicamente y no queremos estar actualizando y cambiando continuamente, por ejemplo el rango de los datos de origen para generar la taba dinámica.

Para ese cometido vamos a necesitar echar mano de VBA y confeccionar un código a medida, para ello he optado por realizar el siguiente ejercicio:
– Usar la grabadora de macros para grabar creación de la tabla dinámica.
– “Tunear” la macro para que realice lo que necesito.

La macro grabada directamente sería esta:

Sub Macro1()
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"DATOS!R1C1:R371C4", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="TRANSPONER!R1C1", TableName:="Tabla dinámica1", _
DefaultVersion:=xlPivotTableVersion10
Sheets("TRANSPONER").Select
Cells(1, 1).Select
With ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("ID")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("NOMBRE")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("FECHA")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("Tabla dinámica1").AddDataField ActiveSheet.PivotTables _
("Tabla dinámica1").PivotFields("IMPORTE"), "Suma de IMPORTE", xlSum
Range("A6").Select
ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("ID").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
Range("A33").Select
ActiveSheet.PivotTables("Tabla dinámica1").ColumnGrand = False
Range("U2").Select
ActiveSheet.PivotTables("Tabla dinámica1").RowGrand = False
End Sub

Pero este código solo nos va a servir para reproducir la tabla con los datos que tenemos en este momento, si aumentamos el rango o queremos cambiarla de ubicación tendremos que modificar la tabla. Para automatizar el proceso debemos por lo tanto, solucionar el primer lugar el problema del los rangos variables, (que se decida incluir a más personas en el rango o eliminarlas). Esto lo podemos hacer agregando una variable a la fuente de los datos que indique el final de los datos, es decir, la última celda que tiene datos.

En la macro original la fuente de datos es SourceData:= “DATOS!R1C1:R371C4”, es decir, va desde la fila 1 de la columna 1 hasta la fila 371 de la columna 4.

Si definimos una variable (a la que llamaremos “final”) podremos solucionar el problema:

Dim final As Double
final = Application.CountA(Worksheets(“DATOS”).Range(“a:a”))

Donde final es el total del contador de ContarA. y quedaría así la fuente de datos:
SourceData:=”DATOS!R1C1:R” & final & “C4”

Ahora que ya tenemos esta parte, solo tenemos que incluir en la primera parte del código, que cada vez que se ejecute la macro se limpie o elimine cualquier dato en la hoja “TRANSPONER”, dado que no se puede generar una tabla dinámica encima de otra. También, al final de la macro debemos colocar una parte del código que copie y pegue valores, de forma que desaparezca la tabla dinámica y se queden solo datos para mejor manipulación.

Os dejo aquí la macro totalmente modificada o “tuneada” jeje. Os dejo en verde las instrucciones comentadas para que sea más sencillo de comprender. En algunos casos he eliminado parte de código que no era necesario y en otros he añadido algunas instrucciones, como por ejemplo, evitar el refresco de pantalla u ocultar los registros que aparezcan en blanco.

Sub Transponer()
'CREAMOS LA VARIABLE PARA DETERMINAR LA LONGITUD DE LOS DATOS
Dim final As Double
final = Application.CountA(Worksheets("DATOS").Range("a:a"))
Application.ScreenUpdating = False
'SELECCIONAMOS LA HOJA TRANSPONER Y ELIMINAMOS TODOS LOS DATOS
Sheets("TRANSPONER").Select
Sheets("TRANSPONER").Cells.Select
Selection.Delete Shift:=xlUp
'SELECCIONAMOS LA HOJA DATOS
Sheets("DATOS").Select
'CREAMOS LA TABLA DINÁMICA, LOS DATOS SE ACTUALIZAN AUTOMÁTICAMENTE CON LA VARIABLE "FINAL"
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"DATOS!R1C1:R" & final & "C4", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="TRANSPONER!R1C1", TableName:="Tabla dinámica1", _
DefaultVersion:=xlPivotTableVersion10
'LLEVAMOS LA TABLA A LA HOJA TRANSPONER
Sheets("TRANSPONER").Select
'INDICAMOS QUE SE GENEREN LOS DATOS DESDE LA FILA 1 Y LA COLUMNA 1
Cells(1, 1).Select
'AGREGAMOS LA PRIMERA COLUMNA "ID" Y OCULTAMOS DATOS EN BLANCO
With ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("ID")
.Orientation = xlRowField
.Position = 1
End With
'AGREGAMOS LA SEGUNDA COLUMNA CON EL CAMPO "NOMBRE"
With ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("NOMBRE")
.Orientation = xlRowField
.Position = 2
End With
'AGREGAMOS LA FILA DE DATOS HORIZONTALES "FECHA"
With ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("FECHA")
.Orientation = xlColumnField
.Position = 1
End With
'AGREGAMOS EL IMPORTE AL CONTENIDO DE LA TABLA
ActiveSheet.PivotTables("Tabla dinámica1").AddDataField ActiveSheet.PivotTables _
("Tabla dinámica1").PivotFields("IMPORTE"), "Suma de IMPORTE", xlSum
'OCULTAMOS SUBTOTALES DEL CAMPO "ID"
ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("ID").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
'OCULTAMOS EL TOTAL GENERAL DE COLUMNA
ActiveSheet.PivotTables("Tabla dinámica1").ColumnGrand = False
'OCULTAMOS EL TOTAL GENERAL DE FILA
ActiveSheet.PivotTables("Tabla dinámica1").RowGrand = False
'*COPIAMOS Y PEGAMOS VALORES. ELIMINAMOS LA PRIMERA FILA DE LA TABLA DINAMICA
With Sheets("TRANSPONER")
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A1").Select
End With
Application.ScreenUpdating = True
'*PARA VER LA TABLA DINÁMICA SOLO HAY COMENTAR LA PARTE DEL CODIGO DE COPIAR Y PEGAR VALORES
'PARA QUE NO REALICE ESTA ULTIMA INSTRUCCION
End Sub

Como habéis podido comprobar, el resultado son los datos con formato valores y no como tabla dinámica, la idea es que sirvan para trabajarlos como si se tratase de un tabla “normal”, pero si queréis que se quede como una tabla dinámica, tan solo tenéis que comentar* (o eliminar) la última parte de la macro (os lo he indicado en el código mostrado) y no convertirá en valores el formato.

* Doy por supuesto que lo sabéis, pero “comentar” es marcar al principio de cada línea de código con comillas simples.

Pues este es el ejemplo de hoy, espero que os sirva y sobre todo os invito a que grabéis vuestras propias macros y luego las personalicéis según vuestras necesidades.

Para probar el archivo, seleccionar la hoja “DATOS” y pulsar el botón “TRANSPONER DATOS”.

No lo he podido probar en excel 2003 ni en 2007, pero supongo que funcionará, si genera algún error, agradezco que me lo comentéis.  🙂

Descarga el archivo de ejemplo pulsando en: TRANSPONER DATOS PERSONALIZADOS