y Excel VBA code to copy paste values

Excel VBA code to copy paste values

vba-copy-paste

In this tutorial, we are going to learn how to copy-paste values using excel VBA. There are many ways to copy paste values using VBA, we will one by one.

The first method is what I recommend, and it is easy to use by referencing cell value with another cell address.

Method 1: - By setting the cell value
Copy a single cell

One of the ways to copy a cell is by setting the cell value with the value of another cell. The below code copies the text in A1 to the B1 cell.

Range("B1").Value = Range("A1").Value
Result of the above code:
vba-copy-paste

As you can see, the above code copied the text in A1 to B1.

Copy multiple cells in a range:

To copy cells in a range, we will set the cell references also in a range. In the below code, I am copying the values from A1: A10 to C1: C10.

Range("C1:C10").Value = Range("A1:A10").Value
Output:
vba-copy-paste
Copy from another worksheet:

Like we did before, to copy data to another worksheet we need to state from which sheet to which sheet we want to copy and also the range of particular sheet. The below code demonstrates how to copy values between worksheets.

Worksheets("Sheet1").Range("A1").Value = Worksheets("Sheet2").Range("A1").Value
vba-copy-paste

As you can see the text in sheet2 got copied to the sheet1 with their respective cell as we needed.

Copy from another workbook:

Here we are going to be more specific like from which workbook we want to copy and paste and secondly in which worksheet it has to be copied and lastly the range of that sheet.

Workbooks("Your_book_name.xlsx").Worksheets("Sheet1").Range("A1").Value = _ Workbooks("Your_book_name.xlsx").Worksheets("Sheet1").Range("A1").Value
vba-copy-paste

Note: Both workbooks have to be open to copy, or else you get an error.

Method 2:  Using range.copy()
Copy a single cell
Range("A1").Copy Range("B1")
Copy multiple cells in a range:
Range("A1:A10").Copy Range("B1:B10")
Copy Entire row:
Sheets("sheet1").Rows(1).entirerow.Copy Sheets("sheet1").Rows(2).Select Sheets("sheet1").Paste Application.CutCopyMode = False
Copy Entire column:
Sheets("sheet1").Columns(1).Copy Sheets("sheet1").Columns(2).Select Sheets("sheet1").Paste Application.CutCopyMode = False
Copy from another worksheet:
Worksheets("Sheet1").Range("A1").Copy Worksheets("Sheet2").Range("B1")
Copy from another workbook:
Workbooks("Your_book_name.xlsx").Worksheets("Sheet1").Range("A1").Copy _ Workbooks("Your_book_name.xlsx").Worksheets("Sheet1").Range("A1")
Method 3:  Using Paste special
Range("A1:A10").Copy
Range("D1:D10").PasteSpecial
Application.CutCopyMode = False

Note: Application. Cutcopymode is used to hide the marching ants over the copied range or for clearing the clipboard after copying.

The paste special used here only copies the value, there are many other properties under paste special we will learn more about them in our next tutorial-like copying formulas, formats, etc.

Hope you liked this article, if so please share it with your friends.

Share on:
Most Popular