Ejercicio práctico 5: Cómo filtrar los registros de una tabla, repartiéndolos en varias hojas mediante macros

Escrito por Administrador

25 de agosto de 2021

Vamos a continuar con el ciclo de casos prácticos y a buscar utilidad a todo lo aprendido hasta ahora.

Hoy veremos un ejercicio que a más de uno le va a servir para ahorrarse bastante tiempo en su trabajo diario. Aprenderemos cómo recorrer todos los registros de una tabla y, en función del valor de uno o varios campos, repartirlos entre distintas hojas del mismo libro. También ordenaremos las tablas-resultado en función del criterio que elijamos.

Usaremos de ejemplo un informe diario de una empresa de mantenimiento, en el que se recogen los movimientos de salida del almacén del material utilizado en cada uno de los trabajos.

total-operaciones-matematicas-macros

El significado de los campos es el siguiente:

  • OT: número de la orden de trabajo
  • Descripción OT: descripción de la orden de trabajo
  • Código artículo: código que tiene el artículo en el almacén de la empresa
  • Descripción artículo: descripción del artículo
  • Importe: importe total. Unidades x precio unitario.
  • Unidades: número de artículos que se han usado.
  • Precio unitario: precio por unidad.
  • Fecha: fecha en la que se ha sacado del almacén el artículo.
  • Usuario: trabajador que ha sido el encargado de realizar el trabajo.
  • Tipo de trabajo: Si el trabajo ha consistido en una reparación o una mejora de la instalación.
  • Grupo de trabajo: número del grupo de trabajo al que pertenece.

 

Como veis, están mezclados los movimientos de materiales de distintos grupos de trabajo. La idea de este ejercicio es distribuir la información de la tabla en varias hojas en función del grupo de trabajo de manera automática.

De esta manera, obtendríamos en la hoja “Grupo 1” solo los movimientos de materiales que han realizado las personas que conforman el grupo 1, y lo mismo para el resto de grupos. El resultado deseado se muestra en las siguientes figuras.

total-operaciones-matematicas-macros
total-operaciones-matematicas-macros
total-operaciones-matematicas-macros

¿Cómo podemos conseguirlo? Es muy fácil. Te lo muestro a continuación.

Vamos a crear dos botones. El primero de ellos será el que ejecutará la macro para generar los informes, y el segundo lo usaremos para borrarlos.

Macro 1. Generación de informes de materiales según grupo de trabajo.

Sub Informe()

'Desactivamos el refresco de pantalla para agilizar la macro

Application.ScreenUpdating = False

'Buscamos la última fila con datos de cada hoja

Sheets("Grupo 1").Select

filagrupo1 = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Sheets("Grupo 2").Select

filagrupo2 = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Sheets("Grupo 3").Select

filagrupo3 = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

'Recorremos los registros de la tabla y repartiéndolos en el resto de hojas en función del grupo de trabajo

Sheets("Informe").Select

f = Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row

For i = 2 To f

Sheets("Informe").Select

'Si pertenece al GRUPO 1

If Cells(i, "K") = 1 Then

Range(Cells(i, "A"), Cells(i, "K")).Select

Selection.Copy

Sheets("Grupo 1").Select

Cells(filagrupo1, "A").Select

Selection.PasteSpecial Paste:=xlPasteAll, operation:=xlNone, skipblanks _
:=False, Transpose:=False

filagrupo1 = filagrupo1 + 1

goto salto

End If

'Si pertenece al GRUPO 2

If Cells(i, "K") = 2 Then

Range(Cells(i, "A"), Cells(i, "K")).Select

Selection.Copy

Sheets("Grupo 2").Select

Cells(filagrupo2, "A").Select

Selection.PasteSpecial Paste:=xlPasteAll, operation:=xlNone, skipblanks _
:=False, Transpose:=False

filagrupo2 = filagrupo2 + 1

goto salto

End If

'Si pertenece al GRUPO 3

If Cells(i, "K") = 3 Then

Range(Cells(i, "A"), Cells(i, "K")).Select

Selection.Copy

Sheets("Grupo 3").Select

Cells(filagrupo3, "A").Select

Selection.PasteSpecial Paste:=xlPasteAll, operation:=xlNone, skipblanks _
:=False, Transpose:=False

filagrupo3 = filagrupo3 + 1

End If

salto:

Next

'Calculamos el importe total de cada grupo, dos filas más abajo del último registro

'GRUPO 1

Sheets("Grupo 1").Select

totalizar = Application.WorksheetFunction.Sum(Range(Cells(2, "E"), Cells(filagrupo1, "E")))

Cells(filagrupo1, "E") = totalizar

Cells(filagrupo1, "E").Select

With Selection

.HorizontalAlignment = xlCenter

End With

Selection.Font.Bold = True

'GRUPO 2

Sheets("Grupo 2").Select

totalizar = Application.WorksheetFunction.Sum(Range(Cells(2, "E"), Cells(filagrupo2, "E")))

Cells(filagrupo2, "E") = totalizar

Cells(filagrupo2, "E").Select

With Selection

.HorizontalAlignment = xlCenter

End With

Selection.Font.Bold = True

'GRUPO 3

Sheets("Grupo 3").Select

totalizar = Application.WorksheetFunction.Sum(Range(Cells(2, "E"), Cells(filagrupo3, "E")))

Cells(filagrupo3, "E") = totalizar

Cells(filagrupo3, "E").Select

With Selection

.HorizontalAlignment = xlCenter

End With

Selection.Font.Bold = True

'Llamamos a la macro para ordenar el informe

Call Ordenfinal

'Abrimos la hoja Grupo 1

Sheets("Grupo 1").Select

'Activamos el refresco de pantalla

Application.ScreenUpdating = True

End Sub

Macro 2. Borrado de informes de materiales.

Sub Borrar()

'Desactivamos el refresco de pantalla para agilizar la macro

Application.ScreenUpdating = False

'Mostramos mensaje de aviso porque se borrarán datos

resultado = MsgBox("¿Seguro? Se perderán los cálculos", vbYesNo + vbExclamation, "Advertencia")

Select Case resultado

Case vbNo:

GoTo final

End Select

'Borramos los datos de las hojas de Grupos

Sheets("Grupo 1").Activate

Range("A2:K500").ClearContents

Sheets("Grupo 2").Activate

Range("A2:K500").ClearContents

Sheets("Grupo 3").Activate

Range("A2:K500").ClearContents

'Volvemos a la hoja Informe

Sheets("Informe").Activate

final:

'Desactivamos el refresco de pantalla para agilizar la macro

Application.ScreenUpdating = True

End Sub

Macro 3. Ordenación de informes según importe descendente.

Sub Ordenfinal()

'Ordenamos las hojas de los grupos por el campo importe, de mayor a menor

'GRUPO 1

Sheets("Grupo 1").Select

filagrupo1 = Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row

Range(Cells(2, "A"), Cells(filagrupo1, "K")).Sort Key1:=Range("E:E"), Order1:=xlDescending, Header:=xlNo

'GRUPO 2

Sheets("Grupo 2").Select

filagrupo2 = Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row

Range(Cells(2, "A"), Cells(filagrupo2, "K")).Sort Key1:=Range("E:E"), Order1:=xlDescending, Header:=xlNo

'GRUPO 3

Sheets("Grupo 3").Select

filagrupo3 = Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row

Range(Cells(2, "A"), Cells(filagrupo3, "K")).Sort Key1:=Range("E:E"), Order1:=xlDescending, Header:=xlNo

'Abrimos la hoja Grupo 1

Sheets("Grupo 1").Select

End Sub

Vamos a analizar la primera macro paso a paso.

Lo primero que haremos, prácticamente en todos los desarrollos que hagamos, es desactivar el refresco de pantalla. En este caso es algo obligado, ya que el programa va a estar consultando una tabla, y copiando la información en distintas hojas. Si no desactivamos el refresco, además de estar viendo pestañeos, puede ralentizarse la ejecución del programa.

Sub Informe()

'Desactivamos el refresco de pantalla para agilizar la macro

Application.ScreenUpdating = False

Como vamos a ir copiando registros de la tabla a las hojas de cada grupo, tendremos que saber cuál es la última fila con datos de cada hoja, para copiar el registro seleccionado justo en la siguiente y no solapar información. Por eso, hemos usado un offset(1,0), es decir, al último número de fila que contiene información, le sumamos uno y lo guardamos en la variable. Esto con cada hoja.

'Buscamos la última fila con datos de cada hoja

Sheets("Grupo 1").Select

filagrupo1 = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Sheets("Grupo 2").Select

filagrupo2 = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Sheets("Grupo 3").Select

filagrupo3 = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

En este punto comienzan la verdadera programación para repartir los registros entre las distintas hojas según el grupo de trabajo al que pertenecen.

Para ello hemos preparado un bucle que va a recorrer todos los registros de la tabla. Como no conocemos de antemano el número de registros que trae el informe matriz, el bucle va a barrer desde la fila 2 hasta la fila f, que es la última fila que contiene información en la hoja “Informe”. En este caso el offset es 0.

'Recorremos los registros de la tabla y repartiéndolos en el resto de hojas en función del grupo de trabajo

Sheets("Informe").Select

f = Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row

Comenzamos a barrer los registros de la tabla de la hoja “Informe” para i=2, y comprobamos si el valor de la columna K es 1, es decir, si pertenece al grupo de trabajo 1.

For i = 2 To f

Sheets("Informe").Select

'Si pertenece al GRUPO 1

If Cells(i, "K") = 1 Then

total-operaciones-matematicas-macros

Como vemos en la figura anterior, el primer movimiento de la tabla lo realizó una persona del grupo 1, luego debe ser copiado en su correspondiente hoja.

En este caso vamos a usar un copiado total (valores y formato) usando la propiedad xlPasteAll.

Range(Cells(i, "A"), Cells(i, "K")).Select

Selection.Copy

Sheets("Grupo 1").Select

Cells(filagrupo1, "A").Select

Selection.PasteSpecial Paste:=xlPasteAll, operation:=xlNone, skipblanks _
:=False, Transpose:=False

Una vez que hemos copiado el registro, sumamos una unidad a la variable que almacena el número de fila, para que apunte a la siguiente fila y sea donde se copie el próximo registro del grupo 1.

filagrupo1 = filagrupo1 + 1

Por último, como el registro ya ha sido copiado, ya no hay que seguir comprobando si pertenece a los grupos 2 y 3, luego saltamos a la etiqueta salto y pasamos al siguiente registro.

goto salto

End If

Para los dos siguientes registros (i=3 e i=4), sucede lo mismo. Pertenecen al grupo 1 y son copiados en su correspondiente hoja.

total-operaciones-matematicas-macros

En el caso i=5, al no cumplirse la condición de pertenecer al grupo 1,

'Si pertenece al GRUPO 1

If Cells(i, "K") = 1 Then

Pasaría a la siguiente,

'Si pertenece al GRUPO 2

If Cells(i, "K") = 2 Then

total-operaciones-matematicas-macros

Que sí se cumple, por lo que será copiado en la hoja del grupo 2.

Range(Cells(i, "A"), Cells(i, "K")).Select

Selection.Copy

Sheets("Grupo 2").Select

Cells(filagrupo2, "A").Select

Selection.PasteSpecial Paste:=xlPasteAll, operation:=xlNone, skipblanks _
:=False, Transpose:=False

filagrupo2 = filagrupo2 + 1

goto salto

End If

Y así seguiría hasta i=101, que es la última fila con información de nuestro ejemplo.

Como añadido he querido incluir dos detalles.

El primero es el cálculo del importe total gastado en materiales por cada grupo de trabajo. Este resultado se coloca automáticamente en la siguiente fila a la última con registros de cada hoja, en la columna de “importe”.

Para el cálculo usamos la función Suma aplicada a la columna “importe” del rango de registros copiados a cada hoja. Después simplemente hemos dado un poco de formato centrando el resultado en la celda y marcándolo en negrita.

'Calculamos el importe total de cada grupo, dos filas más abajo del último registro

'GRUPO 1

Sheets("Grupo 1").Select

totalizar = Application.WorksheetFunction.Sum(Range(Cells(2, "E"), Cells(filagrupo1, "E")))

Cells(filagrupo1, "E") = totalizar

Cells(filagrupo1, "E").Select

With Selection

.HorizontalAlignment = xlCenter

End With

Selection.Font.Bold = True

'GRUPO 2

Sheets("Grupo 2").Select

totalizar = Application.WorksheetFunction.Sum(Range(Cells(2, "E"), Cells(filagrupo2, "E")))

Cells(filagrupo2, "E") = totalizar

Cells(filagrupo2, "E").Select

With Selection

.HorizontalAlignment = xlCenter

End With

Selection.Font.Bold = True

'GRUPO 3

Sheets("Grupo 3").Select

totalizar = Application.WorksheetFunction.Sum(Range(Cells(2, "E"), Cells(filagrupo3, "E")))

Cells(filagrupo3, "E") = totalizar

Cells(filagrupo3, "E").Select

With Selection

.HorizontalAlignment = xlCenter

End With

Selection.Font.Bold = True

El segundo detalle del que hablaba, es la ordenación automática de los registros de cada hoja de forma descendente según el importe.

Para ello hemos creado la tercera macro, y simplemente la llamamos desde la primera usando la función call.

'Llamamos a la macro para ordenar el informe

Call Ordenfinal

'Abrimos la hoja Grupo 1

Sheets("Grupo 1").Select

'Activamos el refresco de pantalla

Application.ScreenUpdating = True

End Sub

Como recordatorio para la tercera macro, os dejo el enlace donde explico cómo ordenar una tabla usando macros.

Sub Ordenfinal()

'Ordenamos las hojas de los grupos por el campo importe, de mayor a menor

'GRUPO 1

Sheets("Grupo 1").Select

filagrupo1 = Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row

Range(Cells(2, "A"), Cells(filagrupo1, "K")).Sort Key1:=Range("E:E"), Order1:=xlDescending, Header:=xlNo

'GRUPO 2

Sheets("Grupo 2").Select

filagrupo2 = Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row

Range(Cells(2, "A"), Cells(filagrupo2, "K")).Sort Key1:=Range("E:E"), Order1:=xlDescending, Header:=xlNo

'GRUPO 3

Sheets("Grupo 3").Select

filagrupo3 = Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row

Range(Cells(2, "A"), Cells(filagrupo3, "K")).Sort Key1:=Range("E:E"), Order1:=xlDescending, Header:=xlNo

'Abrimos la hoja Grupo 1

Sheets("Grupo 1").Select

End Sub

La segunda macro simplemente realiza una selección de celdas y borra su contenido. Es muy básica, pero si tenéis cualquier duda, no dudéis en consultármela.

Si quisiéramos que el filtrado tuviese en cuenta otro campo además del grupo de trabajo, por ejemplo importes superiores a 100€, modificaríamos el condicional de la siguiente manera,

'Si pertenece al GRUPO 1 y el importe es superior a 100€

If Cells(i, "K") = 1  and Cells(i, "E")>100 Then

Así, solo se copiarían en la hoja “Grupo 1”, los movimientos superiores a 100€ realizados por personas pertenecientes a este grupo.

Con esto llegamos al final de la vigesimoctava lección. Espero que te esté gustando el curso y que me plantees cualquier duda que te surja. También puedes proponerme macros que te interese desarrollar y las voy incluyendo en el temario, soy todo oídos. Nos vemos en el próximo capítulo.

Descarga el archivo del ejemplo

« EP4. Cómo cruzar datos de distintas tablas con macros.

EP6. Cómo buscar errores con macros. »

0 comentarios

Enviar un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.

Quizá te interese,

Contacto