Solved: Paste last row into master sheet.

[noobie]

:bow: hi all,
i currently have this macro which I've gotten from this forum. But I do not know how to only copy values wthout any conditional formatting nor formulas. Could u tell me how do i proceed from this ?



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






Many thanks. :beerchug:

[acw]

Hi

try


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" & Sheets("records").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)
'Sheets("records").Range("a" & lrow).Resize(1, 8).Value = Range("a" & Target.Row & ":H" & Target.Row).Value
'Sheets("records").Range("i" & lrow).Resize(1, 2).Value = Range("L" & Target.Row & ":M" & Target.Row).Value
'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)
Sheets("records").Range("a" & lrow).Resize(1, 8).Value = Range("a" & Target.Row & ":H" & Target.Row).Value
Sheets("records").Range("i" & lrow).Resize(1, 2).Value = Range("L" & Target.Row & ":M" & Target.Row).Value
'End If
End If

End Sub


I've just commented out some of the duplicate lines. You only need the one output command section as the command for both options is the same. The test for a blank A1 only need to change the lrow value.

HTH

Tony

[noobie]

Thanks Tony. It worked! :friends: