Selection Paste Using VBA

This article is about how we can copy and paste data from one place to another in a sheet using VBA.

Selection Paste Using VBA

Copy-pasting has simplified our daily tasks on Excel. To have a good command of VBA programming, we need to learn the idea of the paste feature.

There are many methods to paste data from one cell to another. The first one is by using an assignment operator, the second one is with the help of the paste function, and, last but not least, by utilizing the pastespecial function.

Let us study the VBA paste method by recording a macro. Suppose we have some values in cell A1 in Excel; now, we will copy and paste from A1 to B3.

The following are the steps we have to follow in recording a macro:

1. First, we must select the Record Macro that we will see under the Excel Developer tab.
2. As soon as we have clicked the Record Macro, the name of the default Macro will appear. Now, we only have to click OK.
3. Now, all our activities are recorded. As of now, we have to select the desired cell, which in our case is A1, to copy.
4. The next step is to press the Ctrl+C command. This will copy our selected cell.
5. Now, it is time to choose the destination cell, B3, where we want to paste our copied stuff using excel.
6. To paste, press the Ctrl+V command. Now, we can stop recording.

The code to select the A1 cell is shown below.

Code:

# vba
Range("A1").Select

When the cell is copied, its code is selected. Copy instead of Range("A1") because it either becomes an active cell or selection after selecting the cell. So here, in coding, it appears as selection.

Now, copy the next cell, A1, and paste it. The code is shown below.

Code:

# vba
Range("B3").Select

Finally, our value is pasted.

The code says Active Sheet because you have selected the cell to paste. It appears as an active cell of an active sheet.

The code for this step uses ActiveSheet.Paste. This concludes the paste method in VBA.

The formula is as shown below.

Expression.Paste (Dest, Url)

You can understand the expression as it is the worksheet name that you have selected to paste.

Copy and Paste in the Same Sheet Using VBA

Now, let’s see how we can use the VBA paste method while working on the same sheet. Suppose we have taken a few values from cell B1 to B5.

We will learn how to write the code according to our needs. Here are some steps:

The first step is to copy the desired data range to paste anything. We will copy the range from B1 to B5 in our case, as shown below.

Code:

# vba
Sub CopyExample()
Range("B1:B5").Copy
End Sub

When we have copied our values, we need to indicate where we want to paste them; for this purpose, we will choose the worksheet name. As we want to paste in the same sheet, we will use the Active Sheet Object, as shown below.

Code:

# vba
Sub CopyExample()
Range("B1:B5").Copy
ActiveSheet.Paste
End Sub

Once we have selected the paste method, we must determine where the destination is. In our case, it is range D1 to D5, as shown below.

Code:

# vba
Sub CopyExample()
Range("B1:B5").Copy
ActiveSheet.Paste Destination:=Range("D1:D5")
End Sub

Output:

We can easily run this code by pressing the F5 key, and this will do the job of copying data from B1 to B5 and pasting it into D1 to D5.

Copy and Paste Special Properties Using VBA

We can also copy and paste particular properties of cells, for example, special formats, values, etc., with the help of this VBA feature. It permits you to perform significant paste operations like skip blanks and transpose.

Let’s go through an example in which we will try to copy and paste some special properties of the cells we copied. We can use xlPasteFormats to paste only the formatting of the copied cells.

We can use xlPasteColumnWidths to paste the width of the columns we copied. We can also use xlPasteFormulas to paste the formulas from our copied cells.

Code:

# vba
Sub SpecialCopying()

Range("B1").Copy

Range("D1").PasteSpecial Paste:=xlPasteFormats

Range("D1").PasteSpecial Paste:=xlPasteColumnWidths

Range("D1").PasteSpecial Paste:=xlPasteFormulas

Application.CutCopyMode = False
End Sub