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),
- Click on Insert a new module.
- Enter the code, save it and run it.
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 SubCut 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 SubCut 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 SubCut 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 SubCut 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 SubValue 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 SubPaste 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 SubClear Clipboard in Excel VBA
To clear the clipboard, enter the following code in the module.
Code to clear clipboard
Application.CutCopyMode = False
