code not to copy formula

[Trebor76]

Hi Peter,

Try this which copies the range from SHEET2 to Sheet3 as values:

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("SHEET2")
            Set ws2 = Worksheets("Sheet3")
            tRow = Target.Row
            nRow = ws2.Cells(Rows.Count, 1).End(xlUp).Row + 1
            Application.ScreenUpdating = False
            ws1.Range("A" & tRow).Resize(, 9).Copy
            ws2.Range("A" & nRow).PasteSpecial xlPasteValues    '(, 8) adjust for # columns to copy
            Application.CutCopyMode = False
            Set ws1 = Nothing
            Set ws2 = Nothing
            Application.ScreenUpdating = True
        End If
    End If

End Sub

HTH

Robert