PASTING FROM ONE WORKSHEET TO ANOTHER INTO NEXT FREE ROW

[rollis13]

Found this in my notes. As it is the macro, when you place a "Y" in the check column, copies the first 8 columns to the destination sheet. Have a try:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim tRow As Long
    Dim nRow As Long
    
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("M:M")) Is Nothing Then                'adjust for check column
        If UCase(Target.Value) = "Y" Then
            Set ws1 = Worksheets("ORIGIN")
            Set ws2 = Worksheets("DESTINATION")
            tRow = Target.Row
            nRow = ws2.Cells(Rows.Count, 1).End(xlUp).Row + 1
            ws1.Range("A" & tRow).Resize(, 8).Copy ws2.Range("A" & nRow)  '(, 8) adjust for # columns to copy
        End If
    End If
End Sub