PASTING FROM ONE WORKSHEET TO ANOTHER INTO NEXT FREE ROW

[SheppeyMan]

I need to copy part of particular row on ORIGIN sheet (range En:AJn where n is the row number) into the next available row of another sheet called DESTINATION. To complicate matters, the first three rows in the original sheet are fixed titles so do not get copied.

[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