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:

  1. Select the source range
  2. Copy the Selection to the Clipboard. Home > Clipboard > Copy, or Ctrl + C shortcut
  3. Activate the target Worksheet
  4. Select the upper left cell of target range
  5. 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:

  1. Line 6: assigns the top left cell of the Source range to the Src range object. This is equivalent to:
  2.                 Set Src = WorksheetFunction.Index(Range("Source"), 1, 1)  ' Home cell of Source
    
  3. Line 13: uses the colon (:) statement separation character, where two statements are combined. This is equivalent to:
  4.                 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:

  1. Line 27: uses the PasteSpecial method with explicit arguments by name. Lines 27 to 30 are equivalent to:
  2.                  Range("Target").Offset(i, 0).PasteSpecial xlPasteValues, xlNone, False, False
     
  3. with arguments shown by position

The corresponding Paste Special dialog box, with default arguments, is shown in figure 1.


xlf paste special dialog
Fig 1. - Paste Special dialog box - with default options for each of the three groups. Paste, Operation, and Skip blanks / Transpose


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

  1. Code 3 Line 50 is equivalent to:
  2.             Src.Offset(i, 0).Copy Tgt.Offset(i, 0)
     
  3. 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