Skip to main content

Excel Macro: Copy and Paste a Range

One of the most common action you’ll need to learn is copying and pasting a range of data. It’s very easy to do this manually. In normal data, we use either CTRL + C to copy a selection of data and then use CTRL + V to paste the selected data in the target cell. It’s just as easy to copy and paste via VBA.

Range.Copy method

Range.Copy Method is a very convenient Method to copy and paste Range to destination in one line of code. All the formatting and formulas will be copied and pasted.

Syntax

expression.Copy (Destination)

Destination is optional. If this argument is omitted, Microsoft Excel copies the range to the Clipboard. It returns Variant.

Example 1: Copy Range and paste to another worksheet

The below code copy Sheet1 A1 to Sheet2 B1.

Sheets("Sheet1").Range("A1").Copy (Sheets("Sheet2").Range("B1"))

If there are more than one Range to copy, you just need to specific the first Range of the destination.

Sheets("Sheet1").Range("A1:B1").Copy (Sheets("Sheet2").Range("B1"))

Example 2: copy row

The below code copy from Sheet1 row 1:2 to Sheet2 row 6.

Note that if you copy a row to destination, the destination Range must be a Row or a Range in column A.

Sheets("Sheet1").Rows("1:2").Copy (Sheets("Sheet2").Range("A6"))

OR

Sheets("Sheet1").Rows("1:2").Copy (Sheets("Sheet2").Rows("6:6"))

OR

Sheets("Sheet1").Rows("1:2").Copy (Sheets("Sheet2").Rows("6:7"))

Example 3: copy row and paste to new inserted row

ActiveCell.EntireRow.Copy
ActiveCell.EntireRow.Insert

Example 4: copy column

The below code copy Sheet1 column A:B to Sheet2 column B:C (because it pastes at B1).

Sheets("Sheet1").Columns("A:B").Copy (Sheets("Sheet2").Range("B1"))

Example 5: copy multiple Range to a new Workbook

In case the copied Columns are not adjacent to each other, Set a Range to combine those Columns (or Rows).

The below code copy range1 and then paste to A1 of new workbook.

Sub CopyToNewWorkbook()
     Set range1 = Range("A:B, E:F")
     range1.Copy
     Set newbook = Workbooks.Add
     Range("A1").PasteSpecial
 End Sub

Example 6: destination argument omitted

The following code example inspects the value in column D for each row on Sheet1. If the value in column D equals A, the entire row is copied onto SheetA in the next empty row. If the value equals B, the row is copied onto SheetB.

Sub CopyRows()
    Sheets("Sheet1").Select
    ' Find the last row of data
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    ' Loop through each row
    For i = 2 To FinalRow
        ' Decide if to copy based on column D
        ThisValue = Cells(i, 4).Value
        If ThisValue = "A" Then
            Cells(i, 1).Resize(1, 33).Copy
            Sheets("SheetA").Select
            NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
            Cells(NextRow, 1).Select
            ActiveSheet.Paste
            Sheets("Sheet1").Select
        ElseIf ThisValue = "B" Then
            Cells(i, 1).Resize(1, 33).Copy
            Sheets("SheetB").Select
            NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
            Cells(NextRow, 1).Select
            ActiveSheet.Paste
            Sheets("Sheet1").Select
        End If
    Next i
End Sub

Range.PasteSpecial method

When working with your spreadsheet, you likely often have to copy formulas and paste them as values. To do this in a macro, you can use the PasteSpecial method.

Syntax

expression.PasteSpecial (Paste, Operation, SkipBlanks, Transpose)

It returns Variant.

Parameters

Name Required/Optional Data type Description
Paste Optional XlPasteType Specifies the part of the range to be pasted.
Operation Optional XlPasteSpecialOperation Specifies how numeric data will be calculated with the destinations cells on the worksheet.
SkipBlanks Optional Variant True to have blank cells in the range on the clipboard not be pasted into the destination range. The default value is False.
Transpose Optional Variant True to transpose rows and columns when the range is pasted. The default value is False.

XlPasteType enumeration

Name Value Description
xlPasteAll -4104 Everything will be pasted.
xlPasteAllExceptBorders 7 Everything except borders will be pasted.
xlPasteAllMergingConditionalFormats 14 Everything will be pasted and conditional formats will be merged.
xlPasteAllUsingSourceTheme 13 Everything will be pasted using the source theme.
xlPasteColumnWidths 8 Copied column width is pasted.
xlPasteComments -4144 Comments are pasted.
xlPasteFormats -4122 Copied source format is pasted.
xlPasteFormulas -4123 Formulas are pasted.
xlPasteFormulasAndNumberFormats 11 Formulas and Number formats are pasted.
xlPasteValidation 6 Validations are pasted.
xlPasteValues -4163 Values are pasted.
xlPasteValuesAndNumberFormats 12 Values and Number formats are pasted.

XlPasteSpecialOperation enumeration

Name Value Description
xlPasteSpecialOperationAdd 2 Copied data will be added to the value in the destination cell.
xlPasteSpecialOperationDivide 5 Copied data will divide the value in the destination cell.
xlPasteSpecialOperationMultiply 4 Copied data will multiply the value in the destination cell.
xlPasteSpecialOperationNone -4142 No calculation will be done in the paste operation.
xlPasteSpecialOperationSubtract 3 Copied data will be subtracted from the value in the destination cell.

Example 1: paste values

Sub PasteSpecialValues()
    Sheets("Sheet1").Range("A1:D5").Copy
    Sheets("Sheet1").Range("A7:D11").PasteSpecial xlPasteValues
    'Remove the animation around the copied cell
    Application.CutCopyMode = False
End Sub

Note: To remove the animation around the copied cell add below code:

Application.CutCopyMode = False

Example 2: paste formats

Sheets("Sheet1").Range("A1:D5").Copy
Sheets("Sheet1").Range("A7:D11").PasteSpecial xlPasteFormats

Example 3: paste formulas

Sheets("Sheet1").Range("A1:D5").Copy
Sheets("Sheet1").Range("A7:D11").PasteSpecial xlPasteFormulas

Example 4: EntireRow copy and paste

Sub EntireRowCopy()
    'Paste the data of row 3 in row 8
    Worksheets("Sheet1").Activate
    Rows(3).EntireRow.Copy
    'PasteSpecial Method without argument copies the format as well.
    Rows(8).EntireRow.PasteSpecial
End Sub

Example 5: Multiply

This example replaces the data in cells A1:C2 on Sheet1 with the multiply of the existing contents and cells D1 on Sheet1.

1
2
A B C D
1 2 3 3
4 5 6

VBA Code

Sub pasteMultiply()
    Range("D1").Copy  'D1 contains value 3
    Range("A1:C2").PasteSpecial Operation:=xlPasteSpecialOperationMultiply
End Sub

Result

1
2
A B C D
3 6 9 3
12 15 18

Example 6: Add

This example replaces the data in cells D1:D2 on Sheet1 with the sum of the existing contents and cells A1:A2 on Sheet1.

1
2
A B C D
1 2 3 3
4 5 6 3

VBA Code

Sub PasteSpecialOperationAdd()
    With Worksheets("Sheet1")
        .Range("A1:A2").Copy
        .Range("D1:D2").PasteSpecial Operation:=xlPasteSpecialOperationAdd
    End With
End Sub

Result

1
2
A B C D
1 2 3 4
4 5 6 7

Most VBA code should be placed in Standard Modules unless specified.

If you see a comment '------------------ Modules------------------ in the code header that means put the code in a Standard Module. For more information, learn this course: Where should I put the Excel VBA code?

The following steps teach you how to put VBA code into a Standard Module:

  1. Activate the Visual Basic Editor by pressing ALT + F11.
  2. Right-click the project/workbook name in the Project Window.
  3. Choose Insert -> Module.
  4. Type or paste the code in the newly created module. You will probably need to change the sheet name, the range address, and the save location.
  5. Click Run button on the Visual Basic Editor toolbar.
  6. For more information, learn this course: Programming with Excel VBA

Leave a comment

Your email address will not be published. Required fields are marked *

Format your code: <pre><code class="language-vba">place your code here</code></pre>

8 comments
  1. SL
    SLewisNeAli

    Тюльпаны в Минске - это прекрасный выбор для создания весеннего настроения в вашем доме или офисе. Закажите Купить цветы в Минске прямо сейчас!

  2. ZA
    Zabi

    could you please help me to prepare VBA code.
    I have 1000rows of data.
    I have to copy each time 73 rows and paste in new sheets..

  3. ED
    Edwin

    I am trying to run this macro but every time I run it; it pastes the data over riding the same cell... how to change

    Sub Save_Record()
    Range("N2:AX2").Select
    Selection.Copy
    Sheets("Land Prep Data Entry").Select
    Range("Table3[Farmer ID]").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets("Calculator").Select
    Range("C5").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("C8:E8").Select
    Selection.ClearContents
    Range("D10").Select
    Selection.ClearContents
    Range("D14").Select
    Selection.ClearContents
    Range("D17:D20").Select
    Selection.ClearContents
    Range("D23:D31").Select
    Selection.ClearContents
    Range("D34:D37").Select
    Selection.ClearContents
    Range("I30").Select
    Selection.ClearContents
    End Sub

    • EX

      @Edwin You didn't specify a specific target cell to copy, you need to get the last row data position of the table, I guess your data needs to be transposed.

      Sub Save_Record()
          With Sheets("Land Prep Data Entry")
              Dim icol As Integer
              icol = .Range("Table3[Farmer ID]").Column 'Get the column
              Dim LastBlankCell As Integer
              LastBlankCell = .Cells(Rows.Count, icol).End(xlUp).Row + 1 'Get the last blank cell
              
              Worksheets("Sheet1").Range("N2:AX2").Copy
              .Cells(LastBlankCell, icol).PasteSpecial Transpose:=True
          End With
          
          With Sheets("Calculator")
              Range("C5").ClearContents
              Range("C8:E8").ClearContents
              Range("D10").ClearContents
              Range("D14").ClearContents
              Range("D17:D20").ClearContents
              Range("D23:D31").ClearContents
              Range("D34:D37").ClearContents
              Range("I30").ClearContents
          End With
      End Sub
  4. SJ
    Sami Jumppanen

    Range("A1") format is not very handy in macros. Usually there is a need to use column and row numbers. Range(Cells(row, column)...) is generic feature, but for some reason for .Copy everything does not work. I haven't yet found clear documentation about this.

  5. DK
    DKC

    how to Enable Micro Setting Excel 2007 in VBA Coding throuw