How to Copy and Paste in Excel Using VBA (7 Methods)

Copy and Paste is the most frequent task done in Excel. In addition to worksheet functions, we can also copy and paste data in VBA. In this article, I’ll show you 7 ways to copy and paste data in Excel VBA.

Let’s say, we have a dataset of jobs and towns of different persons. Now I’ll show you how you can copy and paste in VBA using this dataset.

Download Practice Workbook

7 Ways to Copy and Paste with Excel VBA

1. Copy and Paste a Single Cell

You can copy and paste a single cell by creating a macro in Microsoft Visual Basic Applications (VBA). First of all,

➤ Press ALT+F11 to open the VBA window.

Now, In the VBA window, from the Project- VBAProject panel,

➤ Right click on the sheet name to open a dropdown menu, click on Insert to expand and then select Module.

It will open a Module(Code) window.

➤ Insert the following code in this window,

Sub Paste_SingleCell()

Range("D5").Copy Range("F5")

End Sub

The Code will copy the cell D5 and will paste it in cell F5.

After that,

➤ Click on the Run icon in the VBA window and then close the VBA window.

As a result, you will see that cell D5 has been copied and pasted in cell F5.

Read More: How to Copy a Cell in Excel Using Formula(7 Methods)

2. Excel VBA to Copy and Paste a Range of Cells

To copy and paste a range of cells using Excel VBA,

➤ Press ALT+F11 to open the VBA window.

Now, In the VBA window, from the Project- VBAProject panel,

➤ Right click on the sheet name to open a dropdown menu, click on Insert to expand and then select Module.

As a result, a Module(Code) window will be opened.

➤ Type the following code in the Module(Code) window,

Sub Copy_Range()

Range("B5:C7").Copy Range("F5:G7")

End Sub

 The code will copy cell range B5:C7 and will paste it in cell range F5:G7.

After inserting the code,

➤ Click on the Run icon.

As a result, a Macro window will be opened

➤ Select Copy_Range from the Macro Name box and click on Run.

It will execute the Macro in your Excel worksheet. Now,

➤ Close the VBA window.

In your Excel worksheet, you will see, all the cells which are given in the Macro code have been copied to the mentioned destination.

Read More: How to Copy and Paste Multiple Cells in Excel (7 Quick Ways)

3. Copy and Paste an Entire Column with Excel VBA

You can also copy and paste an Entire Column by using Excel VBA. To do that, first,

➤ Press ALT+F11 to open the VBA window.

Now, In the VBA window, from the Project- VBAProject panel,

➤ Right click on the sheet name to open a dropdown menu, click on Insert to expand and then select Module.

As a result, a Module(Code) window will be opened.

➤ Type the following code in the Module(Code) window,

Sub Copy_column()

 Range("D:D").Copy Range("F:F")

End Sub

This will copy column D and will paste the column in Column F.

After that,

➤ Close the VBA window.

Now, to execute the code in your worksheet,

➤ Go to the VIEW tab and click on Macro.

It will open the Macro window.

➤ Select Copy_column from the Macro Name box and click on Run.

As a result, you will see column D has been copied into column E.

Read More: Macro to Copy Specific Columns from One Worksheet to Another in Excel

Similar Readings:

• How to Copy and Paste in Excel Without Changing the Format
• Copy and Paste Exact Formatting in Excel(Quick 6 Methods)
• How to Copy Merged and Filtered Cells in Excel (4 Methods)
• Run Time Error 1004: PasteSpecial Method of Range Class Failed
• How to Paste Link and Transpose in Excel (8 Quick Ways)

4. Excel VBA to Copy and Paste Entire Row

You can also copy and paste an Entire Row by using Excel VBA. First,

➤ Press ALT+F11 to open the VBA window.

Now, In the VBA window, from the Project- VBAProject panel,

➤ Right click on the sheet name to open a dropdown menu, click on Insert to expand and then select Module.

As a result, a Module(Code) window will be opened.

➤ Type the following code in the Module(Code) window,

Sub Copy_Row()

Range("10:10").Copy Range("12:12")

End Sub

This will copy row 10 and will paste the row in row 12.

After that,

➤ Click on the Run icon.

As a result the Macro window will be opened.

➤ Select Copy_Row from the Macro Name box and click on Run.

After closing the VBA window, you will see, row 10 has been copied into row 12.

Read More: How to Copy Rows Automatically in Excel to Another Sheet (4 Methods)

5. Copy and Paste to Another Worksheet Using Excel VBA

Excel VBA can also be used to copy data from one worksheet to another. In this example we will copy data from a worksheet named Copy to a worksheet named Paste. First,

➤ Press ALT+F11 to open the VBA window.

Now, In the VBA window, from the Project- VBAProject panel,

➤ Right click on the sheet name to open a dropdown menu, click on Insert to expand and then select Module.

As a result, a Module(Code) window will be opened.

➤ Type the following code in the Module(Code) window,

Sub Copy_AnotherSheet()

Worksheets("Copy").Range("B4:D10").Copy Worksheets("Paste").Range("B4:D10")

End Sub

The code will copy the cells B4:D10 from the Copy sheet and paste it  in cells B4:D10 of the Paste sheet.

After inserting the code,

➤ Click on the Run icon and then close the VBA window.

Now, If you open the Paste sheet, you will see cells B4:D10 from the Copy sheet have been copied and pasted in this sheet.

Read More: How to Copy a Worksheet in Excel (5 Smart Ways)

Similar Readings

• Use VBA to Paste Values Only with No Formatting in Excel
• How to Copy and Paste Visible Cells Only in Excel (3 Easy Ways)
• Copy and Paste is Not Working in Excel (9 Reasons & Solutions)
• How to Copy Multiple Rows in Excel Using Macro (4 Examples)
• Excel Formula to Copy Cell value to Another Cell

6. Copy and Paste to Another Workbook

Excel VBA can also be used to copy data from one workbook to another. Suppose, we have a new workbook named Paste by VBA.xlsm. Now, we will copy our dataset from the current workbook (Copy and Paste in Excel VBA.xlsm) to this workbook.

First, open the Paste by VBA workbook and switch back to your current workbook.

After that,

➤ Press ALT+F11 to open the VBA window.

Now, In the VBA window, from the Project- VBAProject panel,

➤ Right click on the sheet name to open a dropdown menu, click on Insert to expand and then select Module.

A Module(Code) window will be opened.

➤ Insert the following code in this window,

Sub Copy_AnotherWorkbook()

    Workbooks("Copy and Paste in Excel VBA.xlsm").Worksheets("Workbook").Range("A1:D10").Copy _
    Workbooks("Paste by VBA.xlsm").Worksheets("Paste").Range("A1:D10")

End Sub

The code will copy our dataset from the current workbook (Copy and Paste in Excel VBA.xlsm)  to the Paste by VBA.xlsm workbook.

After inserting the code,

➤ Click on the Run icon and then close the VBA window.

Now, if you open the new workbook, you can see dataset from the previous workbook has been copied into this workbook.

Read More: Macro to Copy Data from One Workbook to Another Based on Criteria

7. Copy and Paste Using Paste Special in VBA

You can Copy and Paste particular cell characteristics with Paste Special (examples: formats, values, column widths, etc.). You can also use it to execute custom paste operations such as skip blanks and transpose. Now, I’ll show you how you can use paste special in VBA by copying the format of our dataset. First,

➤ Press ALT+F11 to open the VBA window.

Now, In the VBA window, from the Project- VBAProject panel,

➤ Right click on the sheet name to open a dropdown menu, click on Insert to expand and then select Module.

As a result a Module(Code) window will be opened.

➤ Insert the following code in this window,

Sub Paste_special()

Range("B4:D10").Copy
Range("F4:H10").PasteSpecial Paste:=xlPasteFormats

End Sub

The code will create a macro named Paste_special which will only copy the formats of cell range B4:D10 and will paste it in cell range F4:H10.

After that,

➤ Close the VBA window.

Now, to execute the macro,

➤ Go to the View tab and click on Macro.

It will open the Macro window.

➤ Select Paste_special from the Macro Name box and click on Run.

As a result, you will see, only the formats of the cells will be copied and pasted to the mentioned destination.

Read More: VBA Paste Special to Copy Values and Formats in Excel (9 Examples)

Conclusion

I hope now you know how you can copy and paste in Excel VBA. If you have any type of confusion, please feel free to leave a comment.

Related Articles

• VBA Code to Compare Two Excel Sheets and Copy Differences
• [Fixed]: Right Click Copy and Paste Not Working in Excel (11 Solutions)
• PasteSpecial Method of Worksheet Class Failed (Reasons & Solutions)
• Excel Formula to Copy Cell Value from Another Sheet
• Copy Unique Values to Another Worksheet in Excel (5 Methods)
• Excel VBA: Copy Range to Another Workbook
• Macro to Copy and Paste from One Worksheet to Another (15 Methods)