Distribuir datos en hojas Excel con macros.

sábado, enero 19, 2008

En la nota anterior sobre cómo distribuir datos de una hoja a otras hojas de un cuaderno Excel, vimos como hacerlo con fórmulas.

En esa nota señalaba que el método con fórmulas es útil sólo si nuestro cuaderno contiene pocos datos. Uno de mis lectores intentó usar la solución con fórmulas pero, en sus porpias palabras "no he podido concluir mi base de datos porque la cantidad de data es mucha y con el peso de las formulas todas las maquinas se cuelgan".

Este mismo lector me pedía que le enseñara la forma de hacerlo con macros. En esta nota mostraré una solución posible con macros al mismo caso que mostrábamos en la nota anterior.
Debo aclarar que esta solución no es óptima desde el punto de vista de programación, sino que está orientada al usuario promedio cuyo objetivo es crear herramientas para el trabajo diario y no desarrollo de implementaciones en Excel.

El ejemplo se basa en el mismo archivo de la nota anterior. El archivo con las macros puede descargarse ">distribuir_a_hojas_macroaquí.

Empezamos por agregar el encabezamiento "Transferido".





Esta columna nos servirá para chequear si la línea de la hoja Datos ha sido transferida o no.

En esta solución usaremos dos macros. Una de tipo evento y otra que hará el trabajo de copiar los datos a la hoja correspondiente.
La idea es que, una vez agregados los datos en la hoja "Datos", haciendo doble-clic en la celda correspondiente de la columna E, los datos sean transferidos a la hoja adecuada. Además queremos evitar que por error los datos sean transferidos más de una vez. Para eso usaremos el mecanismo de poner, automáticamente, la palabra "SI" en la columna Transferido, después de haber copiado los datos. Una vez que aparece la palabra SI en la columna E de la línea, la macro no permitirá volver a copiarlos.

Para disparar la macro que copie los datos, usaremos el evento BeforeDoubleClick de la hoja "Datos". Empezamos por abrir el editor de Vba y haciendo doble-clic en el icono de la hoja Datos, abrimos el módulo de Vb para el evento



En el módulo del evento ponemos este código


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim rngAnotar As Range, Resp, Fila

Set rngAnotar = [E:E]

If IsEmpty(Target) = False Then
MsgBox "La línea ya fue transferida"
Exit Sub
End If

If Union(Target, rngAnotar).Address = rngAnotar.Address Then
Resp = MsgBox(prompt:="Transferir datos?", Buttons:=vbYesNo, _
Title:="Transferir Datos")
If Resp = vbNo Then
Cancel = True
Exit Sub
End If
Fila = Target.Row
Call dist_hojas
Target.Value = "SI"
End If
End Sub


Después de declara las variables y definir el rango E, empezamos por comprobar si la línea a sido transferida en el pasado. Si la palabra SI aparece en la celda correspondiente de la línea que queremos copiar, aparece un mensaje informándonos que ya hemos transferido los datos y la macro se interrumpe.
El resto del código produce un mensaje preguntándonos si queremos copiar los datos. Sólo si apretamos NO, la macro se interrumpe y no habrá ningún cambio en el cuaderno.



Si aceptamos, los datos serán copiados y en la celda de la columna E aparecerá la palabra SI



Si intentamos volver a copiar los datos de la línea, aparece este mensaje



El trabajo de copiar a la hoja correspondiente lo hace esta macro, que ponemos en un módulo corriente (Módulo1)


Sub dist_hojas()
Dim LastRow As Long, Hoja As String
Dim rngCopiar As Range

Hoja = "Ruta " & ActiveCell.Offset(0, -1).Value 'definir nombre de hoja

Set rngCopiar = Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 3))

Application.ScreenUpdating = False

With Sheets(Hoja)
.Activate
LastRow = WorksheetFunction.CountA([A:A]) + 1
End With

rngCopiar.Copy Sheets(Hoja).Cells(LastRow, 1)
Sheets("Datos").Select

Application.ScreenUpdating = True

End Sub


Después de definir las variable, la primer línea del código "arma" el nombre de la hoja uniendo a la palabra "Ruta" el número que aparece en la columna D, con el operador "&".
La línea siguiente determina cuál es el rango a copiar en "Datos".
El paso siguiente es determinar cuál es la primer fila libre en la hoja en la que vamos a pegar los datos.
Una vez hecho esto, volvemos a "Datos", copiamos el rango adecuado y lo pegamos en la hoja correspondiente.

En este modelo no hemos puesto ningún método automático para borrar lpineas que han sido copiadas y cancelar la señal SI en la hoja Datos. Esto se deberá hacer manualmente.




Technorati Tags:

6 comments:

Eduardo,  23 enero, 2008 20:11  

Jorge: me parece excelente el blog, lejos el mejor que he encontrado en la red. Estoy recien empezando con esto de las macros y tengo una consulta. Mi idea es copiar una lista de datos de una hoja con un boton (llamado Generar Archivo) y que abra una nuevo libro con los datos copiados. Te lo pregunto porque debe ser muy parecido a lo que realizaste en esta entrada.

De antemando gracias adios!!

Jorge L. Dunkelman 24 enero, 2008 17:39  

Hola Eduardo,
te sugiero que abras el grabador de macros y realices la tarea. Cuando terminás, abrís el editor de Vb donde podés ver la rutina creada.
Esta rutina es poco eficiente y además no es flexible, ya que al correrla nuevamente, vuelve a repetir exactamente los pasos de la grabación. Pero podés ver los objetos y los métodos empleados y adaptar el código a tus necesidades.
Tal vez más adelante publique una nota sobre el tema.

Anónimo,  15 diciembre, 2008 20:29  

Hola Jorge:

¿Qué modificación se podría hacer en la macro para distribuir todos los datos de una sola vez en lugar de ir uno a uno?

Gracias,
Sergio

Jorge L. Dunkelman 15 diciembre, 2008 23:16  

En lugar de usar un evento, tendrías que crear una ,acro que copie los datos. Podrías ligar la macro a un botón para activarla.

Anónimo,  14 septiembre, 2009 16:04  

Hola Jorge, muy bueno el blog, necesito saber que modificacion podria hacerle a la macro para que reconozca si existe un valor en la fila anterior y que se pegue por debajo de esta.-

gracias

Jorge L. Dunkelman 14 septiembre, 2009 17:57  

Hola
entiendo que querés transferir varias líneas de una vez. Para eso tenés que crear un loop (bucle) usando Do...Loop o For...Next. Como en la macro de esta nota, empezás por ubicar la primer línea libre (LastRow) y a partir de ahí vas incrementando el valor de la variable cada vez que copias una línea. Espero haberme explicado.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP