VBA Paste

Excel VBA Paste

There are three different ways to paste some data from place to another in a worksheet using VBA, the first method is to normally refer the values from one cell to another cell using the assignment operator, another method is by using the paste function and third method is by using the pastespecial function.

Copy and paste is the most common thing we do day in day out in our workplace. In a regular spreadsheet, we don’t need any special introduction to it. In VBA programming it is very important to understand the concept of the “PASTE” method to understand the program to progress to the next level.

Understand the VBA Paste Method by Recording a Macro

To start off the proceedings lets understand how the paste method works by recording a simple macro. I have entered a value in the cell A1 as shown in the below image.

Now I will show you the method of copy and pasting from cell A1 to A3. Follow the below steps to record a macro.

Step 1: Click on Record Macro under the excel developer tab.

Step 2: The moment you click on record macro it will show you the name of the default macro. As of now just click on OK.

Step 3: The moment you click on ok, it starts recording the activities you do. The first thing you need to do is select the cell A1 to copy.

Step 4: Now press Ctrl + C to copy the selected cell.

Step 5: Now select the cell A3 where we need to paste the copied value.

Step 6: Now paste by pressing Ctrl + V.

Step 7: Now stop the recordings.

Go to a visual basic editor to see the recordings. Below is out recording.

The first thing we have done here is “we have selected the cell A1”, so the code for this activity is Range(“A1”).Select

Next activity is we have copied the cell A1. You can notice here is code is not Range(“A1”).Copy rather it says Selection. Copy, this is because the moment you select the cell it becomes either active cell or selection. So it referred to as Selection. Copy.

The third activity was we have selected the cell A1 to paste, so the code is Range(“A3”). Select

The final activity is we have pasted the value in the cell. Here it says Active Sheet because the moment you select the cell it becomes an active cell of the active sheet. So the code is “ActiveSheet.Paste”

This is how the Paste method works in VBA.

Formula

Below is the formula for VBA paste method:

Expression.Paste (Destination, Link)

An expression is nothing but what is the worksheet name you want to paste. To understand the worksheet object better read our article on “VBA Worksheet”.

You can refer the other worksheet by name if you want to paste in the same worksheet where you have copied you can refer the current sheet by “Active Sheet”.

Destination: After mentioning the worksheet name, we need to refer the destination cell address. For example, if you want to paste in the worksheet “Sale Data” and in the cell A5 to A10 then below is the sample code.

Worksheets(“Sales Data”).Range(“A5:A10”)

If you ignore this argument then whatever the active cell will be treated as the destination cell.

Link: If you wish to create a link to the copied range then you can supply the argument as TRUE or else FALSE.

Examples of Paste Method in Excel VBA

Below are the examples of the Excel VBA Paste Method.

Example #1 – Paste in Same Excel Worksheet

Now let’s see the usage of the VBA paste method in the same sheet. For example a demonstration, I have few values from cell A1 to A5.

Now I want to copy this and paste in C1 to C5. Follow the below steps to write the code on your own.

Step 1: Before posting anything the first thing we need to do is to copy the range of data. So copy the range from A1 to A5.

Code:

Sub Paste_Example1()

 Range("A1:A5").Copy

End Sub

Step 2: After copying, we need to specify where we are pasting. To do this first we need to specify the worksheet name, in this case since we are pasting in the same sheet, use Active Sheet object.

Code:

Sub Paste_Example1()

 Range("A1:A5").Copy
 ActiveSheet.Paste

End Sub

Step 3: After selecting the paste method we need to specify the destination as well. So the destination will be Range C1 to C5.

Code:

Sub Paste_Example1()

 Range("A1:A5").Copy
 ActiveSheet.Paste Destination:=Range("C1:C5")

End Sub

Step 4: Now I don’t want to create any link to this VBA paste method, so I am ignoring then the next argument.

Now run this code using the F5 key or manually, it will copy from A1 to A5 and will paste in C1 to C5.

If you look at the values from C1 to C5, these values are as it is in a cell from A1 to A5. So this VBA paste method copies everything and pastes everything.

Now I will use the LINK argument to see how it works. For LINK argument I have provided TRUE.

Code:

Sub Paste_Example1()

 Range("A1:A5").Copy
 ActiveSheet.Paste Link:=True

End Sub

This will create a link to the cell from A1 to A5.

So it has created a link. One notable missing is the formatting of the cells here. It has not pasted any formatting styles.

Example #2 – Paste in Different Excel Worksheet

Copying from one worksheet to another sheet requires worksheet names. Assume you want to copy the data from the sheet “First Sheet” and you want to paste in the sheet “Second Sheet” below is the way of referring the sheets.

Code:

Sub Paste_Example2()

    Worksheets("First Sheet").Range("A1:A5").Copy
    Worksheets("Second Sheet").Paste Destination:=Range("C1:C5")

End Sub

This will copy data from A1 to A5 from the sheet name called “First Name” then it will paste in the sheet “Second Sheet” in the range C1 to C5.

 

You can download the VBA Paste Template here:- VBA Paste Excel Template

Recommended Articles

This has been a guide to VBA Paste. Here we learn how the Paste method works through Excel VBA Code along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –

• VBA Paste Values
• Clear Contents in VBA
• VBA Copy Paste
• Paste Special using VBA