Solved: Copying rows into worksheet - problem with macro

[noobie]

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]

A few things.

[vba] 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)[/vba] 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.
[vba] Sheets("Records").Range("A" & Rows.Count).End(xlUp).Row[/vba] 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
[vba] If Sheets("Records").Range("A1").Value <> "" Then
lrow = lrow + 1[/vba] 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.
[vba]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[/vba]

[noobie]

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.

Cheers,