PDA

View Full Version : Solved: Copying rows into worksheet - problem with macro



noobie
12-17-2006, 10:45 PM
Hi all,

Previously I had a similar thread : http://www.vbaexpress.com/forum/showthread.php?t=10281

I tweaked the code to get this macro



Private Sub Worksheet_Change(ByVal Target As Range)
Dim lrow As Long
'look at changes in range K2:K35
If Not Intersect(Target, Range("M2:M89")) Is Nothing Then
lrow = Sheets("Records").Range("A" & Rows.Count).End(xlUp).Row
'when not using headings check if something in else row = 1
If Sheets("Records").Range("A1").Value <> "" Then
lrow = lrow + 1

Range("A" & Target.Row & ":H" & Target.Row).Copy Sheets("Records").Range("A" & lrow)

Range("L" & Target.Row & ":M" & Target.Row).Copy Sheets("Records").Range("I" & Target.Row & ":K" & lrow)
Else
Range("A" & Target.Row & ":H" & Target.Row).Copy Sheets("Records").Range("A" & lrow)
Range("L" & Target.Row & ":M" & Target.Row).Copy Sheets("Records").Range("I" & Target.Row & ":K" & lrow)
End If
End If

End Sub



I'm gonna exclude columns I, J and K in the copying. Before that, column M must be filled first to initate the macro.

I must have tweaked it wrongly because everytime it overwrites the previous row of data copied.

Could someone advise me on how to go about doing it?

JimmyTheHand
12-17-2006, 11:52 PM
:hi:
A few things.

Range("A" & Target.Row & ":H" & Target.Row).Copy Sheets("Records").Range("A" & lrow)
Range("L" & Target.Row & ":M" & Target.Row).Copy Sheets("Records").Range("I" & Target.Row & ":K" & lrow) The red part is obviously wrong, or I don't get the logic of it. Instead of Target.Row, lrow should be used. Even so, the copied range (L:M) is 2 columns, the destination range (I:K) is 3 columns, which can cause unexpected results. It might need to be corrected, too.

About overwriting.
Sheets("Records").Range("A" & Rows.Count).End(xlUp).Row This expression above returns the last used cell's rowindex in column A. If this index is used as destination, it will, of course, overwrite the last row, and not add a new one.
To increase the rowindex and avoid overwriting, you use
If Sheets("Records").Range("A1").Value <> "" Then
lrow = lrow + 1 But here increasing lrow depends on content of A1. As long as A1 holds something, anything, the macro works fine. If A1 gets cleared, lrow won't be increased anymore.

I'd suggest the code rewritten this way.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lrow As Long
'look at changes in range K2:K35
If Not Intersect(Target, Range("M2:M89")) Is Nothing Then
lrow = Sheets("Records").Range("A" & Rows.Count).End(xlUp).Row + 1
Rem This way you always get a new row, no matter what.
Range("A" & Target.Row & ":H" & Target.Row).Copy Sheets("Records").Range("A" & lrow)
Range("L" & Target.Row & ":M" & Target.Row).Copy Sheets("Records").Range("I" & lrow & ":K" & lrow)
End If
Rem You will get an empty row at the beginning of the sheet "Records", but you can remove it,
Rem if you want, at once or later on, e.g. this way:
If Application.WorksheetFunction.CountA(Sheets("Records").Rows(1)) = 0 Then Sheets("Records").Rows(1).Delete

End Sub

noobie
12-18-2006, 12:17 AM
I appreciate your step by step detail. In fact, I was actually given the right macro in thr first place. But at last I had to tweak it as my program changes. Thus, the unslightly macro.

Thanks for your explaination. I surely had understood my mistake.:clap:

Cheers,
:beerchug: