Copy and Paste in Excel VBA: 4 Easiest Way (For Beginners)

In this tutorial, we will learn different ways to Copy and Paste in Excel VBA.

Copy and paste are some of the most frequently used activity in Excel.

For copying the data in Excel, select the dataset, which you want to copy and use the keyboard shortcut (CTRL+C), and then go to the destination location, where we want to paste it use the keyboard shortcut (CTRL+V). Our copy and paste activity is complete.

Here in this tutorial, we will learn about the four easiest methods to copy and paste the data in excel VBA.

In this tutorial, we will learn each method one by one and step by step.

First of all, we will set a small data-set, which we will use for this tutorial to copy and paste. And then set our destination area later, different on every new method.

So, we will use the below-given data-set in all four methods.

In this image, we have one source range and four destination area. We will copy the source range and paste it to each destination area with four different methods.

Range.Copy Method

In this method, our task is to copy data from Range (“A1 to A4”) to Range (“C1 to C4”).

First of all, go to the Developer tab and click Visual Basic and insert a new module.

Write code as follows:-

Sub Copy_and_Paste_01()
‘Range.Copy Method
‘first, write the range to be copied and press one space
‘then write destination field, where to paste data
Range(“A1:A4”).Copy Destination:=Range(“C1”)
End Sub

Range.Copy Macro

Note: – if any line contains (‘) character before it, this is not a coding part and will not affect your coding. The coder explains, why this code is being written.

Just type your code (Range (“A1: A4”).copy) and press a single space, write the destination area where you want to paste it.

After writing the destination range, press F8 from the keyboard to execute the code.

And see the result of the coding in the image.

Destination Range = Source Range Method

In this method, we will write the code differently, write the destination field first, and our source data second.

Destination

This is the field where we want to paste our data. In the previous example, we pasted our data in range (“C1: C4”), so here our destination area is (“C1: C4”).

Source Range

Our source data, which we want to copy into another place.

Common Mistake

Many people do mistakes by writing our source range as the destination field. In this situation, we do not get the correct result.

Suppose that we have data in Range (“A1: A4”) and we want to copy this data into another location Range (“D1: D4”).

Here we need to know our destination field first, which is Range (“D1:D4”).

Our source data will remain the same, Range (“A1: A4”).

Write the macro as mentioned in the below box:-

Sub Copy_and_Paste_02()
‘Destination = Source Method
‘Destination – range where to paste data
‘Source data – our base data to be copied
Range(“D1:D4”) = Range(“A1:A4”).Value
End Sub

Destination = Source Code

Run the macro by pressing the F8 key from the keyboard, see in the below image.

This image is explaining the above method.

Pastes Special Method

Paste Special method contains many options to paste the data-set. One data-set may contain many formatting.

Copy command copies all the formats in a single-use and Paste Special method can paste every format separately.

In future blogs, we will learn deeply about the Paste Special feature of Excel.

Here we will copy Range (“A1: A10”) and paste it into range (“H1: H10”).

See the macro in the box below:-

Sub Copy_and_Paste_03()
‘Paste Special Method
‘Copy the source data
Range(“A1:A4”).Copy
‘here our data will be pasted
Range(“E1”).PasteSpecial
‘below code will remove cut_copy_mode from the selected area
Application.CutCopyMode = False
End Sub

Paste Special Macro

See the result in the image below:-

Current Region Method

The current Region is a VBA property to select all the datasets without giving any range. This property will not consider any data range beyond or after any complete blank rows or columns.

As we did in the previous method, we select our data-set by giving a complete range.

In this method, we only need to select the first cell of the data-set and then use the Current Region property to select the rest of the data.

Check this in the below box:-

Sub Copy_and_Paste_04()
‘CurrentRegion Method
‘We select the only range(“A1”)in the whole data
‘CurrentRegion will select the rest of it
Range(“A1”).CurrentRegion.Copy Destination:=Range(“F1”)
End Sub

Current Region Method

After executing the code by pressing (F8), see the result in the below image:-