VBA – Cut, Copy, Paste from a Macro

In this tutorial, you will learn several different methods to Copy & Paste and Cut & Paste using a VBA macro. Read the companion tutorial on Value Pasting and PasteSpecial for more advanced copying and pasting options.

To use this code: Open the Visual Basic Editor (Alt + F11), Insert a new module (Insert > Module) and copy & paste the desired code into the module.

Copy (Cut) and Paste a Single Cell

This example copies or cuts and pastes a single cell, A1 over to B1:

Sub Paste_OneCell()

    'Copy and Paste Single Cell
    Range("A1").Copy Range("B1")

    'Cut and Paste Single Cell
    Range("A1").Cut Range("B1")

End Sub

Copy Selection

If you want to copy the active selection use this:

Sub CopySelection()

'Paste to a Defined Range
Selection.copy range("b1")

'Offset Paste (offsets 2 cells down and 1 to the right
Selection.copy
Selection.offset(2,1).paste

Application.CutCopyMode = False

End Sub

Copy (Cut) and Paste a Range of Cells

This example copies or cuts and pastes a range of cells, A1:A3 over to B1:B3 :

Sub Paste_Range()

    'Copy and Paste a Range of Cells
    Range("A1:A3").Copy Range("B1:B3")

    'Cut and Paste a Range of Cells
    Range("A1:A3").Cut Range("B1:B3")

End Sub

Copy (Cut) and Paste an Entire Column

Below we will demonstrate a couple of quick examples. Read our article on Copying and Pasting Rows and Columns for detailed examples, explanations, and variations.

This example copies or cuts and pastes an entire column, A over to B:

Sub PasteOneColumn()

    'Copy and Paste Column
    Range("A:A").Copy Range("B:B")


    'Cut and Paste Column
    Range("A:A").Cut Range("B:B")

End Sub

Copy (Cut) and Paste an Entire Row

This example copies or cuts and pastes an entire row, 1 over to 2:

Sub Paste_OneRow()

    'Copy and Paste Row
    Range("1:1").Copy Range("2:2")


    'Cut and Paste Row
    Range("1:1").Cut Range("2:2")

End Sub

Copy (Cut) and Paste to Another Worksheet or Workbook

Sub Paste_Other_Sheet_or_Book()

    'Cut or Copy and Paste to another worksheet
    Worksheets("sheet1").Range("A1").Copy Worksheets("sheet2").Range("B1") 'Copy
    Worksheets("sheet1").Range("A1").Cut Worksheets("sheet2").Range("B1") 'Cut

    'Cut or Copy and Paste to another workbook
    Workbooks("book1.xlsm").Worksheets("sheet1").Range("A1").Copy _ 
    Workbooks("book2.xlsm").Worksheets("sheet1").Range("B1") 'Copy
    Workbooks("book1.xlsm").Worksheets("sheet1").Range("A1").Cut _ 
    Workbooks("book2.xlsm").Worksheets("sheet1").Range("B1") 'Cut

    Application.CutCopyMode = False
End Sub

Value Paste

Normally, when you Copy and Paste you Paste all the properties of a cell: formatting, formulas, etc.. Value Pasting allows you to Copy and Paste cells’ values and nothing else. The easiest way to Value Paste in VBA is to define the cell’s value directly:

Sub ValuePaste()

    'Value Paste Cells
    Range("B1").value = Range("A1").value
    Range("B1:B3").value = Range("A1:A3").value

    'Set Values Between Worksheets
    Worksheets("sheet2").range("A1").value = Worksheets("sheet1").range("A1").value

    'Set Values Between Workbooks
    Workbooks("book2.xlsm").Worksheets("sheet1").range("A1").value = _
    Workbooks("book1.xlsm").Worksheets("sheet1").range("A1").value

    Application.CutCopyMode = False
End Sub

Paste Special

Paste Special allows you to Copy and Paste specific properties of cells (examples: formats, values, column widths, etc.). It also allows you to perform special paste operations (examples: skip blanks, transpose). We will look at several examples below, but for an in-depth read our tutorial on Value Pasting and Paste Special.

Sub PasteSpecial()

    'Perform one Paste Special Operation:
    Range("A1").Copy
    'Paste Formats
    Range("B1").PasteSpecial Paste:=xlPasteFormats
    'Paste Column Widths
    Range("B1").PasteSpecial Paste:=xlPasteColumnWidths
    'Paste Formulas
    Range("B1").PasteSpecial Paste:=xlPasteFormulas

    'Perform Multiple Paste Special Operations at Once:
    Range("A1").Copy
    'Paste Formats and Transpose
    Range("B1").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True

    Application.CutCopyMode = False
End Sub

Clear Clipboard

After Copying & Pasting you might want to clear the clipboard (we do in some of the code examples above).  To clear the Excel clipboard, we set Application.CutCopyMode to False:

Application.CutCopyMode = False

This will clear Excel’s clipboard. However, it will not clear the Windows Clipboard. To clear the Window’s clipboard follow the instructions here.