FLASH SALE! - "CHATGPT AND ARTIFICIAL INTELLIGENCE FOR MICROSOFT EXCEL AT 60% OFF" Enroll Now

VBA Paste

Updated on June 11, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

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

VBA Paste

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Paste (wallstreetmojo.com)

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

Understand the VBA Paste Method by Recording a Macro

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.

VBA Paste Macro example 1

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.

VBA Paste Macro example 1-1

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

VBA Paste Macro example 1-2

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.

VBA Paste Macro example 1-3

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

VBA Paste Macro example 1-4

Step 5: Now, select cell A3, where we must paste the copied value.

VBA Paste Macro example 1-5

Step 6: Now, paste by pressing Ctrl + V.

VBA Paste Macro example 1-6

Step 7: Now, stop the recordings.

Go to a Visual Basic Editor to see the recordings. Below is our recording.

VBA Paste Macro example 1-7

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.

Formula

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.

Examples of Paste Method in Excel VBA

Below are examples of the Excel VBA Paste method.

You can download this VBA Paste Excel here – VBA Paste Excel

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.

VBA Paste Example 1

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
Example 1-1

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
Example 1-2

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
Example 1-3

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.

Example 1-4

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
Example 1-5

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

Example 1-7

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
Example 2

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.

VBA Paste Example 2-1

Recommended Articles

This article has been a guide to VBA Paste. Here, we learn how the Paste method works through Excel VBA code, practical examples, and a downloadable template. Below you can find some useful Excel VBA articles: –