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.
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:-
‘first, write the range to be copied and press one space
‘then write destination field, where to paste data
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.
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”).
Our source data, which we want to copy into another place.
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:-
‘Destination = Source Method
‘Destination – range where to paste data
‘Source data – our base data to be copied
Range(“D1:D4”) = Range(“A1:A4”).Value
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:-
‘Paste Special Method
‘Copy the source data
‘here our data will be pasted
‘below code will remove cut_copy_mode from the selected area
Application.CutCopyMode = False
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:-
‘We select the only range(“A1”)in the whole data
‘CurrentRegion will select the rest of it
After executing the code by pressing (F8), see the result in the below image:-
These four methods are the best and easiest ways to copy and paste data in Excel VBA.
Furthermore, details for the Copy and Paste in Excel VBA are provided on the Microsoft Office website.
I hope you find this tutorial useful.
Please feel free to post your comment or suggestion below.
Another Useful Post