VBA – Cut, Copy, Paste from a Macro
In this Article
About 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.
Did you find this VBA tutorial useful? Then share it with your friends and colleagues:
