Comment recopier feuille avec formule

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range
    Set c = Intersect(Target, Columns(9))
    If Not c Is Nothing Then
        If c.Count > 1 Then Exit Sub
        If c = "oui" Then
            Application.ScreenUpdating = False
            c.EntireRow.Copy
            With Sheets("reception luc")
                .Cells(.Cells(Rows.Count, 9).End(xlUp).Row + 1, 1).PasteSpecial
            End With
            Application.EnableEvents = False
            c.EntireRow.Delete
            Application.EnableEvents = True
        End If
    End If
End Sub 

 With Sheets("reception luc")

 With Sheets("expedition")

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range, numlig As Long
    ' suite du code
    ' ...
            With Sheets("reception luc")
                numlig = .Cells(Rows.Count, 10).End(xlUp).Row + 1
                .Cells(numlig, 1).PasteSpecial
                ' rétablissement liste déroulate et mise à suspendu
                .Cells(numlig, 10).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="oui,non,suspendu"
                .Cells(numlig, 10) = "suspendu"
    ' suite du code
    ' ...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range
    Set c = Intersect(Target, Columns(9))
    If Not c Is Nothing Then
        If c.Count > 1 Then Exit Sub
        If c = "oui" Then
            Application.ScreenUpdating = False
            c.EntireRow.Copy
            With Sheets("reception luc")
                numlig = .Cells(Rows.Count, 10).End(xlUp).Row + 1
                .Cells(numlig, 1).PasteSpecial
                ' rétablissement liste déroulate et mise à suspendu
                .Cells(numlig, 10).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="oui,non,suspendu"
                .Cells(numlig, 10) = "suspendu"
            End With
            Application.EnableEvents = False
            c.EntireRow.Delete
            Application.EnableEvents = True
        End If
    End If
End Sub 

Const Nom_Col As String = "certifié"

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Plage As Range

    If Target.Count > 1 Then Exit Sub    'si on modifie plusieurs cellules simultanément
    adDC = Cells(2, Columns.Count).End(xlToLeft).Address
    If Not Intersect(Columns("B"), Target) Is Nothing Then
        If Target.Value = "" Then Exit Sub
        Range("A" & Target.Row).Value = Date
        Range("A" & Target.Row).NumberFormat = "dd/mm/yyyy"
    End If
    LDCol = Split(adDC, "$")(1)    'lettre derniere colonne ligne 2
    NDCol = Range(Split(adDC, "$")(1) & 1).Column    'derniere colonne en nombre
    Set Plage = Range("A2:" & LDCol & 2)    'plage de colonnes ligne 2
    LCol = Chr(Plage.Find(Nom_Col, lookat:=xlWhole).Column + 64)    'lettre colonne "certifié"
    If Application.CountIf(Plage, Nom_Col) > 0 Then    'existe
        If Not Intersect(Target, Range(LCol & "2:" & LCol & "50")) Is Nothing Then    'plage à adapter
            If UCase(Target) = "OUI" Then
                noEvents = True
                nouvlig = Sheets(" reception camion").Cells(Rows.Count, 1).End(xlUp).Row + 1    'index de la première ligne vide dans "Archives"
                Cells(Target.Row, 1).Resize(1, NDCol).Copy
                With Sheets(" reception camion")
                    .Cells(nouvlig, 1).Resize(1, NDCol).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
                    ' rétablissement liste déroulate et mise à suspendu
                    .Cells(nouvlig, 10).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="oui,non,suspendu"
                    .Cells(nouvlig, 10) = "suspendu"
                End With
                noEvents = False
                Application.EnableEvents = False    'désactiver les événements
                Cells(Target.Row, 1).EntireRow.Delete
                Application.EnableEvents = True
            End If
        End If
    Else
        MsgBox "Attention: " & Nom_Col & " n'extiste pas !!!!!!!"
    End If
    Set Plage = Nothing