How to Copy and Paste Cells using VBA in Excel

In the previous article, we learned how to select a cell or a range of cells using VBA. Now we are going to learn how to copy and paste cells or a range of cells using VBA. We can copy and paste a range within the same worksheet, between different worksheets and different workbooks. However, copying and pasting ranges do not require to select range.

So let us start learning.

Copy and Paste a Single Cell using VBA

You would need to practice the VBA code in this article. You can learn where to put your VBA code from here.

Download the practice workbook named Copy and Paste Range of Cells. This also contains two other workbooks Book 1 and Book 2 which can be used in the last section of the blog.

Let us say we need to copy the contents of cell A1 to cell C1. Use the following VBA code.

Sub Paste_single_cell()
Range("A1").Copy Destination:=Range("C1")
End Sub

• Enter this code into a new workbook module and close the VBA editor.
• Press the Alt and F8 keys to open the list of macros. Select Paste_single_cell and run this macro.

As a result, this macro copied cell A1 of the active worksheet and then pasted it to cell C1.

Explanation –

• The Copy method of the Range object copied the source range to the destination range.

Range("A1").Copy                'Copied the contents of cell A1 

• Thereafter we specify the destination range after the Copy to tell where we want to paste the copied range. We can either use Range(“C1”) or Destination:=.Range(“C1”).

Range("A1").Copy Range("C1")
                    or
Range("A1").Copy Destination:=Range("C1")

Here Destination:= is used to tell the destination range parameter.

Copy and Paste a Range of Cells using VBA

We can use the Range object along with the copy method to copy the entire range of cells. The source range is copied using the Copy method and then we specify the upper left cell of the destination range. We do not need to specify the complete range of cells for the destination range where we want to paste the contents.

Let us say we want to copy the range A1:D10 of the current worksheet to the range F1:I10. Use the following VBA code.

Sub Paste_range()
Range("A1:D10").Copy Destination:=Range("F1")
End Sub

• Put this code in the VBA editor’s new workbook module.
• Close the VBA editor and run this macro Paste_range from the list of Marcos (Alt F8).

As a result, the range A1:D10 is copied and then pasted to the range F1:I10.

Explanation –

• We specified the source range using the Range object and used the Copy method to copy the range A1:D10.

Range("A1:D10").Copy

• Thereafter we specify the upper left cell of the destination range to paste the contents.

Range("A1:D10").Copy Destination:=Range("F1")

Paste Cells Across Different Worksheets using VBA

By now, we have been copying and pasting the cells within the active worksheet. Let us suppose we want to copy the range A1:A3 from sheet “Source worksheet” to range A1:A3 in sheet “Destination Worksheet”.

Use the following macro code to copy the range A1:A3 of the Source Worksheet and paste it to A1:A3 of the Destination Range.

Sub Paste_across_worksheets()
Worksheets("Source Worksheet").Range("A1:A3").Copy Destination:=Worksheets("Destination Worksheet").Range("A1:A3")
End Sub

When we run this macro from the list of macros, it would paste range A1:A3 of sheet Source Worksheet to Destination Worksheet.

Explanation –

• Using the Worksheets, we can refer to any of the worksheets of the active workbook.

Worksheets("Source Worksheet").Range("A1:A3").Copy

• This code copied range A1:A3 of the worksheet specified by Worksheets(“Source Worksheet”). The Source Worksheet is the name of the sheet from where we want to copy the range.
• Thereafter we specify the destination range along with the worksheet name as follows.

Worksheets("Destination Worksheet").Range("A1:A3")

• This range becomes the Destination ( where we want to paste the contents ).

Worksheets("Source Worksheet").Range("A1:A3").Copy Destination:=Worksheets("Destination Worksheet").Range("A1:A3")

Paste Across Workbooks

We have been pasting the ranges within a worksheet or worksheets of the active workbooks. Now we will learn to copy a range from the worksheet of one workbook and paste it into the worksheet of another workbook.

We can specify the workbook name as follows.

Sub Paste_across_workbooks()
Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1").Copy Destination:= Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1")
End Sub

• Add this code to the module of the Current workbook.
• Take two new workbooks named Book1.xlsx and Book2.xlsx. Enter data in cell A1 of Book 1.
• Save Book1.xlsx and Book2.xlsx.
• Now we have three opened workbooks in total. Go to the Windows tab and arrange all windows.
• Run the VBA code.

This brings us to an end.

Thank you for reading.