How to Copy and Paste in Excel VBA
In this VBA tutorial we will learn about ‘how to copy and paste in Excel VBA‘. We will learn about Excel copy and paste in VBA and Paste Special feature also. It is very easy to copy Excel data from one place to another place or one sheet to another sheet in VBA but every time is not so easy because some time we need to copy and paste as formula, comments, validation, formats, values etc.
We will learn about “Copy and Paste in Excel VBA” according to below given headings with their image example:-
This is the simple and easy way to copy and paste excel data from one place to another. Macro first copy the range “A1” to “B4” and then move cursor to range “D1” and paste the entire data.
This is second way to write a VBA code to copy and paste data. First select the range, copy selection, go to second range where to paste data and paste it. One this is added in this code “Application.CutCopyMode=False” which represent to remove the copy sign from Excel.
Entire Column Copy and Paste
Copying entire column in Excel and paste it.
Entire Row Copy and Paste In Excel VBA
The example shows how to copy and paste the entire row in Excel.
Method – 03
Copy and Paste Special In Excel VBA
In above methods we learned about copy and paste as simply but we will learn a different way to paste the copied data, called Paste Special Method. When we copy the data and for pasting it instead of simple paste click on “Paste Special” option. See below image :-
In this image we can see a lot of paste special option. We will discuss about each option here in this tutorial.
Copying the range from “A1:B4” this code will copy all things.
Copy and Paste Formula Only
If there is any formula in data set, this code will copy the data and paste with formula.
Copy and Paste Values Only
This code will copy and paste all values without any formula.
Copy and Paste Formats Only
This code will copy and paste only formats not the data.
Copy and Paste Comments Only
This code will copy the formats or data but paste if there is any comments on the data.
Copy and Paste Validation Only
If there is any validation in the data then this code will work.
Copy and Paste All Except Border
If data has any borders then this code will copy the data but not the borders.
Copy and Paste Columns Width In Excel VBA
This code will copy columns width only
Copy Formula And Number Format Only
This code will copy formulas and number formats, like date formats only.
Copy Values And Number Formats Only
This code will copy values and number formats in the data set only.
We have another paste special option tab called Operation
Copy and Paste In Excel VBA With Addition
In this image we can see that we have copied a value in a cell and add the same value to the selected area with the help of paste special method. In this example “2” is added in all selected data.
Copy and Paste In Excel VBA With Subtract
This code will copy a desired cell value and subtract that value from all selected cell data by paste special with subtract option.
Copy and Paste With Multiply
This code will copy a desired cell value and multiply from that value to all selected data by paste special with multiply option.
Copy and Paste With Divide
This code will copy a desired cell value and divide all selected data from that value by paste special with divide option.
Copy and Paste With Skip Blanks
This is one of the best of Paste Special option to copy the data with blank cell and paste only cell containing values and copied blank cell does not over ride with the data.
Copy and Paste With Transpose
With this method we can copy the data in vertical format and paste as horizontal form as we can see in image below.
Copy and Paste in Excel VBA With Link
This code will copy the data and paste it with the link address as shown in picture below.
So in this tutorial we learn about all forms of copy and paste in Excel VBA. Please feel free to share any query or question if you have, I will feel happy to share.