Studies show that good grammar leads to more promotions and higher salaries. Write with Grammarly

Cut, Copy and Paste using a Macro in Excel

In this article , you will be learning about cut, copy and paste options using a VBA macro in your Excel spreadsheet.

Cut, Copy, Paste from a Macro in Excel

As you know, to run the macro code in Excel,

  • Open the Visual Basic Editor (Alt + F11),
Cut, Copy and Paste using a Macro in Excel
  • Click on Insert a new module.
Cut, Copy and Paste using a Macro in Excel
  • Enter the code, save it and run it.
Cut, Copy and Paste using a Macro in Excel

There are different methods to copy or cut and paste the contents in excel. You can copy a cell, a row, a column and paste it into different columns, rows, cells, or even in different workbooks.

Let’s see them in detail.

Cut or Copy and Paste a Single Cell in Excel VBA

Using this you can cut or copy the contents of the cell and paste it in a different cell.

Code for Cut/copy pasting a single cell

Sub Pasting_ACell ()

    Range("A1").Copy Range("B1")

    Range("A1").Cut Range("B1")

End Sub

Cut or Copy and Paste a Range of Cells in Excel VBA

With this you cut or copy and paste a range of celle

Code for Cut or Copy and Paste a Range of Cells

Sub Paste_Range()

        Range("A1:A3").Copy Range("B1:B3")

    Range("A1:A3").Cut Range("B1:B3")

End Sub

Cut or Copy and Paste an Entire Column in Excel VBA

Using this you can cut or copy and paste an entire column

Code for Cut or Copy and Paste an Entire Column

Sub Paste_AColumn()

    Range("A:A").Copy Range("B:B")

    Range("A:A").Cut Range("B:B")

End Sub

Cut or Copy and Paste an Entire Row in Excel VBA

Using this you can cut or copy and paste an entire row.

Code for Cut or Copy and Paste an Entire Row

Sub Paste_ARow()

    Range("1:1").Copy Range("2:2")

    Range("1:1").Cut Range("2:2")

End Sub

Cut or Copy and Paste to Another Worksheet or Workbook

Using this you cut or copy and paste in another worksheet or workbook.

Code for Cut or Copy and Paste to Another Worksheet or Workbook

Sub To_Other_Sheet_or_Book()

    Worksheets("sheet1").Range("A1").Copy Worksheets("sheet2").Range("B1") 'Copy

    Worksheets("sheet1").Range("A1").Cut Worksheets("sheet2").Range("B1") 'Cut

    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 in Excel VBA

Value Paste option, allows the program only to copy the contents of the cell and not its formatting.

Code for Value Paste

Sub ValuePasting_option()

    Range("B1").value = Range("A1").value

    Range("B1:B3").value = Range("A1:A3").value

        Worksheets("sheet2").range("A1").value = Worksheets("sheet1").range("A1").value

    Workbooks("book2.xlsm").Worksheets("sheet1").range("A1").value = _

    Workbooks("book1.xlsm").Worksheets("sheet1").range("A1").value

    Application.CutCopyMode = False

End Sub

Paste Special in Excel VBA

Paste Special allows you to Copy and Paste a specific properties of the contents as its formatting.

Code for Paste Special

Sub Paste_Special_Option()

    Range("A1").Copy

    Range("B1").PasteSpecial Paste:=xlPasteFormats

    Range("B1").PasteSpecial Paste:=xlPasteColumnWidths

    Range("B1").PasteSpecial Paste:=xlPasteFormulas

    Range("A1").Copy

    Range("B1").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:= _

        False, Transpose:=True

    Application.CutCopyMode = False

End Sub

Clear Clipboard in Excel VBA

To clear the clipboard, enter the following code in the module.

Code to clear clipboard

Application.CutCopyMode = False