copie sans format

Option Explicit

Sub Copier()
Dim DerL As Long, plage As Range

    With ActiveSheet
        DerL = .Range("L" & Rows.Count).End(xlUp).Row
        Set plage = .Range("L10:L" & DerL)
        plage.Copy
    End With

    With Feuil1
        .Range("A5").PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = 0
    End With
End Sub

Option Explicit
Sub CommandButton1_Click()
    Dim DerL As Long
    DerL = Range("L65536").End(xlUp).Row
    Range("L10:L" & DerL).Copy
    Feuil1.Range("A5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

DerL = Range("L65536").End(xlUp).Row
Feuil1.Range("A5:A" & DerL - 5).Value = Range("L10:L" & DerL).Value