XL 2016 EXCEL TOUT COLLER SAUF VALEURS

Sub CopieSansValeur()
With Range("F4")                            '<== cellule de destination à adapter
    Range("B4:C7").Copy                     '<== cellule à copier à adapter
    .PasteSpecial Paste:=xlPasteFormats
    .PasteSpecial Paste:=xlPasteComments
    .PasteSpecial Paste:=xlPasteFormulas
    Application.CutCopyMode = False
    .ClearContents
    Range("H11").Select
End With
End Sub

Sub CopieSansValeur()
With Range("F4:G7")                         '<== cellule de destination à adapter
    Range("B4:C7").Copy                     '<== cellule à copier à adapter
    .PasteSpecial Paste:=xlPasteFormats
    .PasteSpecial Paste:=xlPasteComments
    .PasteSpecial Paste:=xlPasteFormulas
    Application.CutCopyMode = False
    .SpecialCells(xlCellTypeConstants, 23).ClearContents
    Range("H1").Select
End With
End Sub

Sub CopieSansValeur()
[A1:F10].Copy [H1]
On Error Resume Next 'si aucune SpecialCell
[H1:M10].SpecialCells(xlCellTypeConstants).ClearContents
End Sub

Sub Copier()
Dim r As Range, dest As Range, decal1&, decal2%
Set r = [A1:F10] 'à adapter
Set dest = [H1] 'à adapter
decal1 = dest.Row - r.Row: decal2 = dest.Column - r.Column
Application.ScreenUpdating = False
r.Copy
dest.PasteSpecial xlPasteFormats 'copie les formats
Application.CutCopyMode = 0
On Error Resume Next 'si aucune SpecialCell
For Each r In r.SpecialCells(xlCellTypeFormulas)
    r.Copy r(1 + decal1, 1 + decal2) 'pour copier les formules
Next
End Sub