Ejercicio práctico 1: Cómo importar y adaptar los datos de otra hoja Excel automáticamente con VBA.

excel-importar-datos

Escrito por Administrador

2 de septiembre de 2021

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,

total-operaciones-matematicas-macros

En la siguiente figura se muestra la plantilla destino en la que vamos a importar los datos de la anterior hoja Excel,

total-operaciones-matematicas-macros

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.

Sub Importar()

'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.

Sheets("Destino").Activate

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.

Sheets("Origen").Activate

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).

Range(Cells(3, "C"), Cells(200, "D")).Copy Destination:=Sheets("Destino").Cells(2, "B")

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.

Range(Cells(3, "F"), Cells(200, "L")).Copy Destination:=Sheets("Destino").Cells(2, "D")

Ahora solo copiamos la columna N de la hoja Origen porque la columna O está vacía.

Range(Cells(3, "N"), Cells(200, "N")).Copy Destination:=Sheets("Destino").Cells(2, "K")

Pasa lo mismo que en el caso anterior. Copiamos solo la columna P porque la columna Q está vacía.

Range(Cells(3, "P"), Cells(200, "P")).Copy Destination:=Sheets("Destino").Cells(2, "L")

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.

Range(Cells(3, "R"), Cells(200, "U")).Copy Destination:=Sheets("Destino").Cells(2, "M")

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.

Sheets("Destino").Activate

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

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.

For i = 2 To h

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.  

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

En la columna “Revisión” procedemos igual.  

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

Por último, seleccionamos todos los datos y les damos formato (tipo de fuente y tamaño).

Range("A1:Q200").Select

Selection.Font.Size = 12

Selection.Font.Name = "Calibri"

Application.ScreenUpdating = True

End Sub

El resultado es el siguiente,

total-operaciones-matematicas-macros

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).

total-operaciones-matematicas-macros

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).

total-operaciones-matematicas-macros

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.

total-operaciones-matematicas-macros

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

  « L23. Enviar automáticamente emails con macros.

EP2. Cómo buscar en un listado 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