Copy and Paste with VBA
1. Worksheet - Copy and Paste
1.0 The worksheet copy and paste sequence
In Excel, to copy and paste a range:
- Select the source range
- Copy the Selection to the Clipboard. Home > Clipboard > Copy, or Ctrl + C shortcut
- Activate the target Worksheet
- Select the upper left cell of target range
- Paste from the Clipboard to the target range. Right Click > Paste, or Ctrl + V shortcut
In VBA, this is equivalent to:
Code 0a: Snippet
Selection.Copy ActiveSheet.Paste
using the Copy method, and Paste method
Range("Sheet1!A2:A10").Select Selection.Copy Range("Sheet2!B2").Select ActiveSheet.Paste
If instead, the user attempts to only use the Range objects directly, then the following code will return an error (Run-time error '438', Object doesn't support this property of method') on the second statement.
Range("Sheet1!A2:A10").Copy Range("Sheet2!B2").Paste ' <-- Returns Run-time error '438'
While Copy is a Method of the Range object, the Paste item is not. The VBE Object Browser lists only the following objects: Chart, Floor, Point, Series, SeriesCollection, Walls and Worksheet as having a member Paste method. Instead, the Range.PasteSpecial method is available as shown in the next section.
1.1 Workbook setup
Code 0b: Macro to
Setup
the structure for the examples in this module
Sub SetUp() Dim i As Integer Worksheets("Sheet1").Activate Names.Add Name:="Source", RefersTo:="=$A$2:$A$10" For i = 0 To 8 Range("A2").Offset(i, 0) = 100 * (i + 1) Range("A2").Offset(i, 1) = 10 * (i + 1) Next i Worksheets("Sheet2").Activate Names.Add Name:="Target", RefersTo:="=$B$2" End Sub
2. Worksheet - Copy and PasteSpecial (single column)
2.1 PasteSpecial version 2a
The Copy PasteSpecial methods are often used in the body of a loop. The user looping through each item in an Excel array, and copying certain items to another location.
Code 2a: Macro
CopyPaste
using the PasteSpecial method (defaults omitted)
Sub CopyPaste() Dim Src As Range Dim Tgt As Range Dim i As Integer Set Src = Range("Source").Range("A1") ' Home cell of Source Set Tgt = Range("Target") Application.ScreenUpdating = False ' More code statements here For i = 0 To Range("Source").Rows.Count If True Then Src.Offset(i, 0).Copy: Tgt.Offset(i, 0).PasteSpecial End If Next i Application.ScreenUpdating = True End Sub
Code 2a by line number:
- Line 6: assigns the top left cell of the Source range to the Src range object. This is equivalent to:
Set Src = WorksheetFunction.Index(Range("Source"), 1, 1) ' Home cell of Source
- Line 13: uses the colon (:) statement separation character, where two statements are combined. This is equivalent to:
Src.Offset(i, 0).Copy Tgt.Offset(i, 0).PasteSpecial
The syntax for the PasteSpecial method, including optional arguments is:
VBA function / property | Syntax |
---|---|
expression.PasteSpecial (method) | .PasteSpecial(Paste, Operation, SkipBlanks, Transpose) |
Arguments | Description |
---|---|
Paste Optional | XlPasteType Name (Value) xlPasteAll (-404) xlPasteAllExceptBorders (7) xlPasteAllMergingConditionalFormats (14) xlPasteAllUsingSourceTheme (13) xlPasteColumnWidths (8) xlPasteComments (-4144) xlPasteFormats (-4122) xlPasteFormulas (-4123) xlPasteFormulasAndNumberFormats (11) xlPasteValidation (6) xlPasteValues (-4163) xlPasteValuesAndNumberFormats (12) |
Operation Optional | XlPasteSpecialOperation Name (Value) xlNone (-4142). From constants enumeration. xlPasteSpecialOperationAdd (2) xlPasteSpecialOperationDivide (5) xlPasteSpecialOperationMultiply (4) xlPasteSpecialOperationNone (-4142) xlPasteSpecialOperationSubtract (3) |
SkipBlanks Optional |
Variant: True or False |
Transpose Optional |
Variant: True or False |
2.2 PasteSpecial version 2b
Code 2b includes the default values for optional arguments to the PasteSpecial method.
Code 2b: Macro
CopyPaste2
using the PasteSpecial method with explicit (default) parameters
Sub CopyPaste2() Dim i As Integer Application.ScreenUpdating = False For i = 0 To Range("Source").Rows.Count Range("Source").Range("A1").Offset(i, 0).Copy Range("Target").Offset(i, 0).PasteSpecial Paste:=xlPasteAll, _ Operation:=xlNone, _ SkipBlanks:=False, _ Transpose:=False Next i Application.ScreenUpdating = True End Sub
Code 2b by line number:
- Line 27: uses the PasteSpecial method with explicit arguments by name. Lines 27 to 30 are equivalent to:
Range("Target").Offset(i, 0).PasteSpecial xlPasteValues, xlNone, False, False
- with arguments shown by position
The corresponding Paste Special dialog box, with default arguments, is shown in figure 1.

3. Copy & Paste - Copy method with Destination argument
Instead of using the Copy, and PasteSpecial combination, the Copy method can be used with an optional destination argument. Code 3 line 50.
Code 3: Macro
CopyPaste4
with Copy destination argument by name
Sub CopyPaste4() Dim Src As Range Dim Tgt As Range Dim i As Integer Set Src = Range("Source").Range("A1") ' Home cell of Source Set Tgt = Range("Target") Application.ScreenUpdating = False For i = 0 To Range("Source").Rows.Count Src.Offset(i, 0).Copy Destination:=Tgt.Offset(i, 0) Next i Application.ScreenUpdating = True End Sub
- Code 3 Line 50 is equivalent to:
Src.Offset(i, 0).Copy Tgt.Offset(i, 0)
- with arguments shown by position
The syntax for the Copy method, including optional arguments is:
VBA function / property | Syntax |
---|---|
expression.Copy (method) | .Copy(Destination) |
Arguments | Description |
---|---|
Destination Optional | Specifies the new range for the target. If blank, the source is copied to the Clipboard |
4. Copy & Paste - using value assignment (multi column)
A fourth option is to use an assignment statement. To assign a to b, the syntax is b = a. See Code 4 line 71.
Code 4: Macro
AssignSrc2Tgt2cols
assign 2-D Source to 2-D Target
Sub AssignSrc2Tgt2cols() Dim Src As Range Dim Tgt As Range Dim i As Integer, j As Integer Set Src = Range("Source").Range("A1") ' Home cell of Source Set Tgt = Range("Target") Application.ScreenUpdating = False For i = 0 To Range("Source").Rows.Count For j = 0 To 1 Tgt.Offset(i, j).Value = Src.Offset(i, j).Value Next j Next i Application.ScreenUpdating = True End Sub
5. Maintenance - clear target
Code 5: Macro
Clear
clears the contents of the target range (one column)
Sub ClearPaste() Dim i As Integer i = Range("Target").CurrentRegion.Rows.Count Range("Target").Resize(i, 1).ClearContents End Sub
- This example was developed in Excel 2016 Pro 64 bit.
- Published: 15 October 2016
- Revised: Friday 24th of February 2023 - 10:37 PM, Pacific Time (PT)