How to Copy and Paste Cells or Range in Excel VBA

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:-

Method -01

VBA Copy and Paste

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.

Method -02

Copy and Paste in VBA

 

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.

Copy entire column

 

Entire Row Copy and Paste In Excel VBA

The example shows how to copy and paste the entire row in Excel.

Copy entire row

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 :-

Excel Paste Special

 

In this image we can see a lot of paste special option. We will discuss about each option here in this tutorial.

Copy All

 

 

 

 

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_Formulas_Only

Copy and Paste Values Only

This code will copy and paste all values without any formula.

Copy_Values_Only

Copy and Paste Formats Only

This code will copy and paste only formats not the data.

Copy_Formats_Only

Copy and Paste Comments Only

This code will copy the formats or data but paste if there is any comments on the data.

Copy_Comments_Only

Copy and Paste Validation Only

If there is any validation in the data then this code will work.

Copy_Validation_Only

Copy and Paste All Except Border

If  data has any borders then this code will copy the data but not the borders.

Copy_Except_Borders

Copy and Paste Columns Width In Excel VBA

This code will copy columns width only

Copy_Column_Width

Copy Formula And Number Format Only

This code will copy formulas and number formats, like date formats only.

Copy_Formula_And_Number_Formats_only

Copy Values And Number Formats Only

This code will copy values and number formats in the data set only.

Copy_Values_And_Number_Format_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.

Paste_With_Addition

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 Subtraction

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_Multiply

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_With_Divide

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.

Paste_With_Transpose

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.

Paste_With_Link

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.

 

Cheers!!

Narendra

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.