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.

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.

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.

Copy and Paste in Excel VBA

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

Copy and Paste in Excel VBA

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.

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.

code

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.

macro

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

Copy and Paste in Excel VBA

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.

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.

code

Now, to execute the code in your worksheet,

➤ Go to the VIEW tab and click on Macro.

macro

It will open the Macro window.

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

Copy and Paste in Excel VBA

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

Copy and Paste in Excel VBA

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


Similar Readings:


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.

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.

code

As a result the Macro window will be opened.

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

macro

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

Copy and Paste in Excel VBA

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.

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.

code

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.

Copy and Paste in Excel VBA

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


Similar Readings


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.

new 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.

Copy and Paste in Excel VBA

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.

code

After inserting the code,

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

Copy and Paste in Excel VBA

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

Copy and Paste in Excel VBA

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.

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.

code

Now, to execute the macro,

➤ Go to the View tab and click on Macro.

macro

It will open the Macro window.

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

macro

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

Copy and Paste in Excel VBA

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

Prantick Bala

Prantick Bala

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo