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:

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

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.

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:

2.2 PasteSpecial version 2b

Code 2b includes the default values for optional arguments to the PasteSpecial method.

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.

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.

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:

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.

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

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)

Copyright © 2011 – 2023 ♦ Dr Ian O'Connor, CPA.   |   Privacy policy

Comment & Reply policy