Comienza el ciclo de casos prácticos con el que trataré de ilustrar todo lo aprendido hasta ahora, enfocándolo de una manera práctica para que le saquéis el máximo partido en vuestras tareas diarias. En este caso, vamos a importar datos desde una plantilla Excel. Borraremos sus datos y los importaremos en otra hoja.
Para darle mayor envergadura al ejercicio, adaptaremos el formato de algunos de ellos en el traspaso de la información. Todo ello de manera automática.
En la figura se muestra la plantilla origen de la información,
En la siguiente figura se muestra la plantilla destino en la que vamos a importar los datos de la anterior hoja Excel,
Como se puede observar, en la plantilla origen existen columnas intercaladas que están vacías, habrá que obviarlas. También se ha omitido la columna “Fecha de creación del pedido” en la plantilla de destino.
Otra diferencia es la inclusión de la columna de Demanda, calculada de la siguiente manera:
- Si las salidas de 2016 > salidas 2015 entonces D = salidas 2016
- Si las salidas de 2016 < salidas 2015 entonces D = (salidas 2016 + salidas 2015) / 2
Por último, cambiaremos el formato de las columnas “Crítico” y “Revisado”, sustituiremos las Y/N por SI (rojo) y NO (verde).
Todo esto habrá que tenerlo en cuenta a la hora de programar. Ahí va el código para importar datos entre hojas Excel.
Macro 1. Borrar datos de la pestaña Destino e importar y adaptar datos de la pestaña Origen.
'Macro que borra los datos de la pestaña Destino, e importa y adapta los datos de la pestaña Origen
Application.ScreenUpdating = False
'Borramos el contenido de la hoja Destino salvo encabezado
Sheets("Destino").Activate
Range("A2:Q200").ClearContents
Range("E2:F200").Interior.Color = RGB(255, 255, 255)
'Copiamos los datos de Origen a Destino teniendo en cuenta las columnas vacías y cambios de orden
Sheets("Origen").Activate
Range(Cells(3, "A"), Cells(200, "A")).Copy Destination:=Sheets("Destino").Cells(2, "A")
Range(Cells(3, "C"), Cells(200, "D")).Copy Destination:=Sheets("Destino").Cells(2, "B")
Range(Cells(3, "F"), Cells(200, "L")).Copy Destination:=Sheets("Destino").Cells(2, "D")
Range(Cells(3, "N"), Cells(200, "N")).Copy Destination:=Sheets("Destino").Cells(2, "K")
Range(Cells(3, "P"), Cells(200, "P")).Copy Destination:=Sheets("Destino").Cells(2, "L")
Range(Cells(3, "R"), Cells(200, "U")).Copy Destination:=Sheets("Destino").Cells(2, "M")
Sheets("Destino").Activate
'Calculo de la DEMANDA
'Buscamos la última fila con datos de la pestaña Destino
h = Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row
For i = 2 To h
If Cells(i, "P").Value > Cells(i, "O") Then
Cells(i, "Q") = Cells(i, "P")
End If
Next
For i = 2 To h
If Cells(i, "P").Value <= Cells(i, "O") Then
Cells(i, "Q").Formula = (Application.Sum(Cells(i, "P"), Cells(i, "O"))) / 2
End If
Next
For i = 2 To h
If Cells(i, "Q").Value < 0 Then
Cells(i, "Q").Value = 0
End If
Next
'Rellenamos la CRITICIDAD como SI/NO en lugar de Y/N
For i = 2 To h
If Cells(i, 5).Value = "Y" Then
Cells(i, 5) = "SI"
Cells(i, 5).Interior.Color = RGB(255, 199, 206)
Else
If Cells(i, 5).Value = "N" Then
Cells(i, 5) = "NO"
Cells(i, 5).Interior.Color = RGB(198, 239, 206)
Else
End If
End If
Next
'Rellenamos la REVISIÓN como SI/NO en lugar de Y/N
For i = 2 To h
If Cells(i, 6).Value = "N" Then
Cells(i, 6) = "NO"
Cells(i, 6).Interior.Color = RGB(198, 239, 206)
Else
If Cells(i, 6).Value = "Y" Then
Cells(i, 6) = "SI"
Cells(i, 6).Interior.Color = RGB(255, 199, 206)
Else
End If
End If
Next
'Elegimos Tipo de letra y tamaño
Range("A1:Q200").Select
Selection.Font.Size = 12
Selection.Font.Name = "Calibri"
Application.ScreenUpdating = True
End Sub
Lo analizamos paso a paso.
Como ya vimos en su día, es conveniente en códigos largos evitar el refresco de pantalla durante las operaciones para evitar consumo de CPU y reducir el tiempo de ejecución de las tareas.
A continuación, te dejo un enlace donde explico cómo usar la función ScreenUpdate en una macro, por si te interesa.
Sub Importar()
Application.ScreenUpdating = False
Antes de nada, borramos el contenido de las celdas de la hoja Destino por si tuviera valores antiguos de otra importación de datos. Con borrar el rango A2:Q200 es suficiente.
También quitamos el color de fondo de las columnas Critico y Revisado, ya que posteriormente vamos a teñir de rojo los valores NO y de verde los SI.
Range("A2:Q200").ClearContents
Range("E2:F200").Interior.Color = RGB(255, 255, 255)
Una vez tenemos limpia la hoja de destino, vamos a importar los datos desde la hoja Excel Origen a la hoja Destino.
En primer lugar, seleccionamos el rango A3:A200, es decir, la primera columna de la hoja Origen, y la copiamos en la primera columna de la hoja Destino.
Range(Cells(3, "A"), Cells(200, "A")).Copy Destination:=Sheets("Destino").Cells(2, "A")
La segunda columna de la hoja Origen la obviamos porque no aparece en la plantilla de la hoja Destino, así que pasamos a la tercera.
Como la tercera y la cuarta columna de la hoja Origen (Tipo Articulo y Articulo) coinciden con las columnas B y C de la hoja Destino, aprovechamos y copiamos 2 columnas de una vez. (Nota. No se han cogido más columnas porque la columna E de la hoja Origen se encuentra vacía y hay que obviarla, es decir, no debe aparecer en la hoja Destino).
Desde la columna F hasta la L de la hoja Origen, coinciden con las columnas desde la D hasta la J de la hoja Destino, y no hay columnas vacías de por medio, así que copiamos las 7 columnas de una vez.
Ahora solo copiamos la columna N de la hoja Origen porque la columna O está vacía.
Pasa lo mismo que en el caso anterior. Copiamos solo la columna P porque la columna Q está vacía.
Ya no hay más columnas vacías y las columnas desde R hasta U de la hoja Origen coinciden con las columnas desde M a P de la hoja Destino, así que copiamos las 4 de una sola vez.
Ya tenemos copiados todos los datos en la hoja de destino. Ahora vamos a calcular la columna “Demanda”. Para ello, buscamos la última fila con datos de la hoja Destino.
h = Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row
Preparamos un bucle para barrer desde la fila 2 hasta la última con datos (fila h), calculando la demanda de la siguiente forma:
- Si las salidas de 2016 (columna P) > salidas 2015 (columna O) entonces D (columna Q) = salidas 2016
- Si las salidas de 2016 (columna P) < salidas 2015 (columna O) entonces D (columna Q) = (salidas 2016 + salidas 2015) / 2
If Cells(i, "P").Value > Cells(i, "O") Then
Cells(i, "Q") = Cells(i, "P")
End If
Next
For i = 2 To h
If Cells(i, "P").Value <= Cells(i, "O") Then
Cells(i, "Q").Formula = (Application.Sum(Cells(i, "P"), Cells(i, "O"))) / 2
End If
Next
Si por cualquier motivo, hemos realizado devoluciones (salidas en negativo), el cálculo de la demanda puede ser negativo, por eso, para esos casos, actualizamos el valor de la demanda a cero.
If Cells(i, "Q").Value < 0 Then
Cells(i, "Q").Value = 0
End If
Next
En la columna “Criticidad”, sustituimos las Y y las N por Si (en rojo) y NO (en verde) respectivamente.
If Cells(i, 5).Value = "Y" Then
Cells(i, 5) = "SI"
Cells(i, 5).Interior.Color = RGB(255, 199, 206)
Else
If Cells(i, 5).Value = "N" Then
Cells(i, 5) = "NO"
Cells(i, 5).Interior.Color = RGB(198, 239, 206)
Else
End If
End If
Next
En la columna “Revisión” procedemos igual.
If Cells(i, 6).Value = "N" Then
Cells(i, 6) = "NO"
Cells(i, 6).Interior.Color = RGB(198, 239, 206)
Else
If Cells(i, 6).Value = "Y" Then
Cells(i, 6) = "SI"
Cells(i, 6).Interior.Color = RGB(255, 199, 206)
Else
End If
End If
Next
Por último, seleccionamos todos los datos y les damos formato (tipo de fuente y tamaño).
Selection.Font.Size = 12
Selection.Font.Name = "Calibri"
Application.ScreenUpdating = True
End Sub
El resultado es el siguiente,
Vamos a ver si se ha calculado correctamente la columna “Demanda”.
En el primer registro podemos ver como las salidas de almacén en el año 2016 (9) fueron superiores a las del año 2015 (4). Según la regla, la demanda estimada sería igual a las salidas del año 2016 (9).
En el tercer registro las salidas de almacén en el año 2016 (40) fueron inferiores a las del año 2015 (59). Según la regla, la demanda estimada sería igual a la media de los dos años ((40+59)/2=50).
En el undécimo registro en el año 2016 se produjeron más devoluciones que salidas (-3) siendo el consumo inferior al año 2015 (2). Según la regla, la demanda estimada sería igual a la media de los dos años ((-3+2)/2= -1). Como la demanda prevista no tiene sentido que sea negativa, hemos actualizado a cero el valor de la misma.
Con esto llegamos al final de la vigesimocuarta lección, en la que has aprendido a importar y adaptar datos de una hoja Excel a otra.
Espero que te esté gustando el curso. Si quieres, puedes proponerme macros que te interese desarrollar y las voy incluyendo en el temario, soy todo oídos.
Por último, quiero invitarte a compartir tus dudas en los comentarios o en el foro. Intentaré ayudarte en todo lo que pueda, y así aprenderemos todos.
Nos vemos en el próximo capítulo.
Si te ha servido y quieres donar
Descarga el archivo del ejemplo
« L23. Enviar automáticamente emails con macros.
EP2. Cómo buscar en un listado con macros. »
0 comentarios