VBA Paste

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

Copy and paste is the most common thing we do daily in our workplace. Therefore, we do not need a special introduction to a regular spreadsheet. However, 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.

Let us understand how the paste method works by recording a simple macro to start the proceedings. First, we have entered a value in cell A1 as shown in the below image.

Now, we will show you the method of copying and pasting from cell A1 to A3. But, first, 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 the "Record Macro," it will show you the name of the default macro. As of now, click on "OK."

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

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

Step 5: Now, select cell A3, where we must 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 our 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.

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

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

The last 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."

Like this, the Paste method works in VBA.

Below is the formula for the VBA Paste method:

Expression.Paste (Destination, Link)

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

You can refer to the other worksheet by name. For example, if you want to paste the same worksheet you copied, you can refer to the current sheet by "Active Sheet."

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

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

If you ignore this argument, the active cell will treat it as the destination cell.

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

Below are examples of the Excel VBA Paste method.

Example #1 - Paste in Same Excel Worksheet

Now, let us see the usage of the VBA Paste method in the same sheet. For example, we have few values from cell A1 to A5 in a demonstration.

We want to copy this and paste it from C1 to C5. But, first, 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. Since we are pasting in the same sheet, use the Active Sheet object in this case.

Code:

Sub Paste_Example1()

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

End Sub

Step 3: We must specify the destination after selecting the paste method. 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: We do not want to create any link to this VBA paste method, so we are ignoring the next argument.

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

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

Now, we will use the LINK argument to see how it works. For LINK's argument, we have provided TRUE.

Code:

Sub Paste_Example1()

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

End Sub

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

So, it has created a link. One notable missing feature 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. For example, assume you want to copy the data from the "First Sheet." You want to paste in the sheet "Second Sheet" below is the way of referring to the sheets.

Code:

Sub Paste_Example2()

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

End Sub

It will copy data from A1 to A5 from the sheet name "First Name," then paste it into the "Second Sheet" sheet in the range C1 to C5.