VBA Paste

VBA Paste

VBA Paste works in the same manner as we do Copy/Cut and Paste in Excel. VBA Paste it the automated version of what we regularly do in regular excel work. Pasting data in any required location is the routine task. In order to perform this activity, we need to define the location from where we will pick the data and location where we will paste it.

How to Use Excel VBA Paste?

We will learn how to use a VBA Paste with few examples in Excel.

VBA Paste – Example #1

Let’s consider any cell in excel which has some text. Here we have kept the text “VBA Paste” in cell B3 as shown below.

Follow the below steps to use VBA Paste.

Step 1: Now go to the VBA window and open a new Module from the Insert menu as shown below.

Step 2: Once we do that, we will get a blank module. In that start writing Subcategory. It is recommended to write subcategory in the name of the function which we are performing.

Code:

Sub VBAPaste1()

End Sub

Step 3: Now first select the Range cell where we have the data which we need to copy. Here our Range cell is B3, followed by .Copy function. This copies the data in Range cell which we have selected.

Code:

Sub VBAPaste1()

Range("B3").Copy

End Sub

Step 4: Now we need a destination cell where we need to paste this copied value. With the help of Destination command select the Range cell. Here we have selected cell D1, where we will see the value.

Code:

Sub VBAPaste1()

Range("B3").Copy Destination:=Range("D1")

End Sub

Step 5: Once done, compile the code and run it.

We will see the cell content of cell B3 is now copied and pasted in cell D3 as shown above.

VBA Paste- Example #2

Let’s consider another example of VBA Paste. For this go to the VBA window and open a module and then follow the below steps.

Step 1: Start writing Subcategory in the name of VBA Paste or in any other name as shown below.

Code:

Sub VBAPaste2()

End Sub

Step 2: Here we will consider the same data or reference cell B3 which we have seen in example-1. Select the Range cell B3 followed by .Copy command. This will copy the data.

Code:

Sub VBAPaste2()

  Range("B3").Copy

End Sub

Step 3: Now select the range cell where we need to paste the copied data. Here we are selecting cells D1 to D3 unlike selecting only one cell as shown in example-1.

Code:

Sub VBAPaste2()

  Range("B3").Copy
  Range("D1:D3").Select

End Sub

Step 4: Once we have copied the data and selected the cells where it needs to get pasted. Now select Paste command with the help of ActiveSheet function. This Activesheet allows the copied cell pasted in range cells.

Code:

Sub VBAPaste2()

  Range("B3").Copy
  Range("D1:D3").Select
  ActiveSheet.Paste

End Sub

Step 5: Now compile the code and run it. We will see, cell value from cell B3 will get pasted from cells D1 to D3.

VBA Paste- Example #3

Another method of VBA Paste is using the manual process which we do for copy and paste.

Follow the below steps:

Step 1: Write Subcategory of VBA Paste as shown below.

Code:

Sub VBAPaste3()

End Sub

Step 2: It is better to give the sequence to Subcategory if you are writing multiple sets of codes in a single file. For pasting anything, first, we need to copy that data. Here we have selected Copy command.

Code:

Sub VBAPaste3()

  Selection.Copy

End Sub

Step 3: Now select the range or reference cell which we need to copy and paste. Here we have Cell D3 with data “VBA Paste” in it.

Code:

Sub VBAPaste3()

  Selection.Copy
  Range("D3").Select

End Sub

Step 4: Now in excel, we select the cells where we need to paste the data by manually or by Shift + Ctrl + Up/Down keys. Here also we will select the location of cells by End(xlUp) command for selecting the range of output cells.

Code:

Sub VBAPaste3()

  Selection.Copy
  Range("D3").Select
  Range(Selection, Selection.End(xlUp)).Select

End Sub

Step 5: Now select the output cell range, here we have selected cells D1 to D3, where we will see the pasted data.

Code:

Sub VBAPaste3()

  Selection.Copy
  Range("D3").Select
  Range(Selection, Selection.End(xlUp)).Select
  Range("D1:D3").Select

End Sub

Step 6: As the function, we need to perform is paste function, so we need to activate the paste command in VBA by ActiveSheet command followed by dot(.) Paste.

Code:

Sub VBAPaste3()

  Selection.Copy
  Range("D3").Select
  Range(Selection, Selection.End(xlUp)).Select
  Range("D1:D3").Select
  ActiveSheet.Paste

End Sub

Step 7: Now, at last, allow CutCopyMode application as FALSE which means are only Copying the data instead of cutting it as well from the reference cell.

Code:

Sub VBAPaste3()

  Selection.Copy
  Range("D3").Select
  Range(Selection, Selection.End(xlUp)).Select
  Range("D1:D3").Select
  ActiveSheet.Paste
  Application.CutCopyMode = False

End Sub

Step 8: Before running the code put the cursor on Cell B3 first. Then compile the code and run it. We will see our data will get copied to cells D1 to D3.

VBA Paste- Example #4

There is another way to using VBA Paste in a single line of code.

Follow the below steps:

Step 1: Write the subcategory in the sequence and name of a performed function.

Code:

Sub VBAPaste4()

End Sub

Step 2: Use Worksheets command and select the Sheet where we will work. Here our sheet name is “Sheet1”. We can use Workbook.Sheet(1) also for selecting “Sheet1” instead of this.

Code:

Sub VBAPaste4()

Worksheets ("Sheet1").

End Sub

Step 3: Select the cell from where we will copy the data. Here our Range cell is B3 which has the same data as we have seen in the above examples.

Code:

Sub VBAPaste4()

Worksheets ("Sheet1").Range("B3").

End Sub

Step 4: Now insert the Copy command after a dot(.) for copying the data from the selected Range cell.

Code:

Sub VBAPaste4()

Worksheets("Sheet1").Range("B3").Copy

End Sub

Step 5: Now use Destination command to select the location where we will paste the copied data. For this select Worksheet as “Sheet1” which we did at the beginning of this example and select the Range cells anywhere in the same sheet. Here we have selected Range output cells from D1 to D3.

Code:

Sub VBAPaste4()

Worksheets("Sheet1").Range("B3").Copy Destination:=Worksheets("Sheet1").Range("D1:D3")

End Sub

Step 6: Now Compile and run. We will the text “VBA Paste” from cell B3 will get copied and pasted in cells D1 to D3 as shown below.

Pros of Excel VBA Paste

• Applying the first 2 examples are the easy and quickest way to apply VBA Paste.
• For huge data where it is difficult to do multiple activities, there if we automate this kind of small function, that would save huge time to doing manual things.
• There is no limit of copy and pasting the data.

Things to Remember

• Use subcategory in such a way that if you are using multiple modules then putting the sequence in that will make easy for tracking and choosing the right macro.
• Save excel file in Macro Enable Format so that written code will be preserved for next time of use.
• If you are writing multiple lines of code it is recommended to compile the code line by line to find if there is an error in it.
• It is recommended to put the cursor at cell B3 if you are using example-3.

Recommended Articles

This has been a guide to VBA Paste. Here we discussed VBA Paste and how to use Excel VBA Paste along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA Match | How to Use?
2. Excel TRANSPOSE Formula
3. VBA Range | Easy Excel Macros
4. Nested IF Formula in Excel