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