Copy and PasteSpecial

Copy puts a range on the clipboard and PasteSpecial pastes values, formats, or formulas. Reset CutCopyMode afterwards to drop the marching ants.

Code

Range("A1:B10").Copy
Range("D1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

• Paste values

  Range("A1:B10").Copy Range("D1").PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False → Values pasted at D1

  The clean pattern.

• Direct copy

  Range("A1:B10").Copy Destination:=Range("D1") → Copy everything in one line, no clipboard mode

  Simplest full copy.

• Paste formats

  rng.Copy rng.PasteSpecial xlPasteFormats Application.CutCopyMode = False → Formats only

  Copy styling.

• Assign .Value = .Value directly when you only need values; it is faster than the clipboard.
• PasteSpecial Transpose:=True flips.
• Copy across workbooks works the same way.

• Clipboard use fails when another app locks the clipboard.
• Forgetting CutCopyMode = False leaves the border and can paste later by accident.