In this Article
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:
|
1 2 3 4 5 6 7 8 9 |
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 |
VBA Coding Made Easy
Stop searching for VBA code online. Learn more about AutoMacro – A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!
Copy Selection
If you want to copy the active selection use this:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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 :
|
1 2 3 4 5 6 7 8 9 |
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:
|
1 2 3 4 5 6 7 8 9 10 |
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:
|
1 2 3 4 5 6 7 8 9 10 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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:
|
1 |
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.
