GENERAR HOJAS SEGÚN MATRIZ DE REGISTROS ÚNICOS

Un amigo me pidió hace unos días que subiera al blog algo relacionado con la generación de informes automáticos. Después de estarlo pensando varios días, creo que he dado con lo que me estaba pidiendo.

La idea es generar información de forma automática a partir de una base de datos y que esta se pueda «pintar» en una plantilla para luego generar la misma información según el ítem deseado. Para entenderlo, lo mejor es poner un ejemplo y explicarlo.

Imaginemos que somos los propietarios de dos empresas de alimentación y que parte de nuestro trabajo es la distribución a la pequeña empresa de la mercancía que luego pondrá a la venta al cliente minorista. Nuestras empresas cuentan con camiones, conductores, contables, administrativos y comerciales. Estos últimos son los encargados de conseguir clientes y colocar el producto en el mercado a través de diferentes canales de distribución.

Ha finalizado el mes de Enero y queremos conocer el volumen de ventas que ha realizado cada uno de los comerciales de ambas empresas, para ello, nuestro director financiero nos entrega un listado en Excel con toda la información que necesitamos.

Una vez que hemos analizado la información, decidimos que a partir de ese momento, el director financiero debe enviar mensualmente a cada jefe de ventas de las dos empresas el detalle por empleado de facturación por producto y el coste que cada comercial supone para la empresa.

La información solicitada en datos agrupados sería esta:

Como podéis observar se detallan todo tipo de datos de ventas por productos y también los gastos que han tenido los comerciales por comidas y por transporte, básicamente gasolina y peajes. Además se especifican las dos empresas y las delegaciones que lleva cada comercial.

Dado que el generador va a trasladar a cada hoja del libro la información por cada comercial, es imprescindible crear un ID único para cada empleado, esto lo podemos hacer de muchas formas, para este caso, he optado por combinar la empresa a la que pertenecen y la delegación que les corresponde, dado que tenemos una única delegación es sencillo crear una clave única. Para hacerlo automático utilizo la fórmula «concatenar» que ya he explicado varias veces en el blog y creo una columna al final:

Y luego introduzco la fórmula que marco con un círculo rojo en la imagen de arriba:

=CONCATENAR(A3;"_";(EXTRAE(B3;1;4)))

Ahora que tenemos un ID único, tan solo nos falta hacer la plantilla donde vamos a volcar los datos. Yo he diseñado esta, pero podéis crear el modelo que más os guste. En la plantilla podéis dejar las celdas con el formato que necesitéis y también podéis dejar campos calculados con fórmulas. Esta es la plantilla:

Ahora toca el momento colocar la macro en nuestro editor VBA. Abrimos un módulo nuevo y pegamos el siguiente código:

Sub GENERAR_INFORME()
'DEFINIMOS VARIABLES
Dim ORIGEN As Worksheet, HojaNueva As Worksheet
Set INFO = Sheets("COMERCIALES")
Dim i As Double
Dim final As Double
Dim bBien As Boolean
'DEFINIMOS LONGITUD Y FIN DEL PROCESO
For i = 1 To 65000
If INFO.Cells(i + 2, 1) = "" Then
final = i
Exit For
End If
'CONTROLAMOS LOS ERRORES QUE SE PUEDAN PRODUCIR
On Error GoTo ControlError
bBien = True
Sheets("PLANTILLA").Copy After:=Worksheets(Worksheets.Count)
Set PROXIMA_HOJA = Sheets(Worksheets.Count)
NOMBRE_PROXIMA_HOJA = Worksheets("COMERCIALES").Cells(2 + i, 16).Value
'DAMOS EL NOMBRE DE LA CELDA CLAVE A LA PROXIMA HOJA QUE SE GENERE
PROXIMA_HOJA.Name = NOMBRE_PROXIMA_HOJA
'TRASLADAMOS LOS DATOS DE LA HOJA COMERCIALES A LA PLANTILLA
'EMPRESA
INFO.Cells(2 + i, 1).Copy
PROXIMA_HOJA.Cells(4, 3).PasteSpecial Paste:=xlValues
'DELEGACION
INFO.Cells(2 + i, 2).Copy
PROXIMA_HOJA.Cells(4, 7).PasteSpecial Paste:=xlValues
'EMPLEADO
INFO.Cells(2 + i, 3).Copy
PROXIMA_HOJA.Cells(6, 3).PasteSpecial Paste:=xlValues
'MES
INFO.Cells(2 + i, 4).Copy
PROXIMA_HOJA.Cells(2, 3).PasteSpecial Paste:=xlValues
'PUESTO
INFO.Cells(2 + i, 5).Copy
PROXIMA_HOJA.Cells(8, 3).PasteSpecial Paste:=xlValues
'COSTES DE PERSONAL
'GASTOS DE VIAJE
INFO.Cells(2 + i, 6).Copy
PROXIMA_HOJA.Cells(12, 3).PasteSpecial Paste:=xlValues
'COMIDAS
INFO.Cells(2 + i, 7).Copy
PROXIMA_HOJA.Cells(13, 3).PasteSpecial Paste:=xlValues
'VENTAS POR PRODUCTO
'REFRESCOS
INFO.Cells(2 + i, 8).Copy
PROXIMA_HOJA.Cells(12, 7).PasteSpecial Paste:=xlValues
'BEBIDAS
INFO.Cells(2 + i, 9).Copy
PROXIMA_HOJA.Cells(13, 7).PasteSpecial Paste:=xlValues
'MARISCOS
INFO.Cells(2 + i, 10).Copy
PROXIMA_HOJA.Cells(14, 7).PasteSpecial Paste:=xlValues
'FRUTAS
INFO.Cells(2 + i, 11).Copy
PROXIMA_HOJA.Cells(15, 7).PasteSpecial Paste:=xlValues
'VERDURAS
INFO.Cells(2 + i, 12).Copy
PROXIMA_HOJA.Cells(16, 7).PasteSpecial Paste:=xlValues
'CARNES
INFO.Cells(2 + i, 13).Copy
PROXIMA_HOJA.Cells(17, 7).PasteSpecial Paste:=xlValues
'PESCADOS
INFO.Cells(2 + i, 14).Copy
PROXIMA_HOJA.Cells(18, 7).PasteSpecial Paste:=xlValues
'VARIOS
INFO.Cells(2 + i, 15).Copy
PROXIMA_HOJA.Cells(19, 7).PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
'PROTEGEMOS LAS HOJAS NUEVAS CON CONTRASEÑA, PARA NO SE MODIFICADAS POR EL USUARIO FINAL
ActiveSheet.Protect "123"
Next i
'CUANDO ACABE EL PROCESO, SE MOSTRARÁ EL USERFORM1
UserForm1.Show
'SI SE PRODUCE UN ERROR, SE MOSTRARÁ EL SIGUIENTE MENSAJE
Salir:
On Error Resume Next
If Not bBien Then
MsgBox "LA APLICACIÓN NO PUEDE GENERAR LA INFORMACIÓN, VERIFICA QUE HAS ELIMINADO LAS PESTAÑAS GENERADAS Y QUE LOS NOMBRES DE LAS HOJAS NO SON IGUALES."
End If
ControlError:
bBien = False
Resume Salir
End Sub

Una vez que hayamos colocado el código en el módulo uno, y hayamos introducido el userform1, ya podremos generar las plantillas de forma automática. Solo tenemos que crear un Botón para ejecutar la macro desde la hoja «Comerciales» y listo 😉

Y esto es todo, ahora solo os queda descargar el archivo y echar un vistazo, merece la pena ponerlo en práctica cuando tenemos que hacer informes periódicos y con la misma estructura de información.

Descarga el archivo pulsando en: GENERADOR DE INFORME DE VENTAS POR EMPLEADO