3 Ways of Copy/Paste Value, Formatting, Formulas in VBA / Excel
The Range object in VBA has copy method that can be used to copy/paste the contents from one cell or range of cells to other cells.
- 1. How to copy/paste the value of cells in VBA/Excel
- 2. An example of copying one cell to another in the same sheet
- 3. Copying range of cells example
- 4. Using the assignment operator to copy
- 5. Copy Multiple Cells example by value
- 6. Using copy with ActiveSheet paste method example
- 7. Copying contents to another sheet in the same Workbook
- 8. VBA program to copy from one Workbook to another
- 9. Second Approach – Use Copy/Paste methods
- 10. Does Copy/Paste also copy formulas?
- 11. Conclusion:
How to copy/paste the value of cells in VBA/Excel
The Range object in VBA has copy method that can be used to copy/paste the contents from one cell or range of cells to other cells.
You may copy/paste contents to another sheet of the same Workbook or another Workbook sheet as well.
Syntax:
expression.Copy (Destination)
There, the expression represents a range object.
An example of copying one cell to another in the same sheet
In the first example, we will copy the content of B3 cell to the F3 cell by using Copy method.
Code:
|
1 2 3 4 5 |
Sub copy_ex() Range("B3").Copy Destination:=Range("F3") End Sub |
Result:
Copying range of cells example
Now we will copy B3 to B8 cells content to the F3 to F8 cells by using copy method:
|
1 2 3 4 5 6 7 |
Sub copy_ex() 'Copy/Paste multiple cells Range("B3:B8").Copy Destination:=Range("F3:F8") End Sub |
Output:
You can see, not only the value copied but also the formatting as well to the destination cells.
Using the assignment operator to copy
If you just need to copy the value and not formatting then you may use the following two approaches as well to copy/paste:
Range(“F3”) = Range(“B3”)
The following code shows with output:
|
1 2 3 4 5 |
Sub copy_ex() Range("F4") = Range("B4") End Sub |
Output:
Or more explicitly use .Value produces the same result:
|
1 2 3 4 5 |
Sub copy_ex() Range("F4").Value = Range("B4").Value End Sub |
It produces the same result as above.
In both cases, you see it does not copy the formatting but just the value of the cell.
Copy Multiple Cells example by value
|
1 2 3 4 5 |
Sub copy_ex() Range("F4:F10").Value = Range("B2:B8").Value End Sub |
Result:
Using copy with ActiveSheet paste method example
Another way of copying the value as well as formatting is using the copy with paste method.
The Paste is ActiveSheet method that you can use to copy the contents to the selected cell/range.
The example below copies the contents of B4:B8 to the F4:F8 range as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Sub copy_ex() 'Specify range to copy Range("B4:B8").Copy 'Seelct range of destination Range("F4:F8").Select 'Use paste method ActiveSheet.Paste End Sub |
Output:
Formatting is also copied to the destination range.
Copying contents to another sheet in the same Workbook
We will copy the B1:E10 range of the “Product Information” worksheet to “Sheet2”’ A1:D10 cells.
|
1 2 3 4 5 |
Sub copy_ex() Range("B1:E10").Copy Destination:=Worksheets("Temp_Products").Range("A1:D10") End Sub |
Result:
VBA program to copy from one Workbook to another
The following VBA program copies a range of cells from one Workbook to another.
|
1 2 3 4 5 |
Sub copy_ex() Workbooks("source.xlsx").Worksheets("Product Information").Range("B1:D10").Copy Destination:=Workbooks("dest.xlsx").Worksheets("My_sheet2").Range("A1:C10") End Sub |
In that case:
- xlsx is the one you want to copy data from
- xlsx is the destination workbook
Second Approach – Use Copy/Paste methods
The following program uses copy/paste methods:
|
1 2 3 4 5 6 7 8 9 10 11 |
Sub copy_ex() Workbooks("source.xlsx").Worksheets("Product Information").Range("B1:D10").Copy Workbooks("dest.xlsx").Activate ActiveWorkbook.Worksheets("Temp_Prod").Select ActiveSheet.Paste End Sub |
In both cases, workbooks should be open.
Does Copy/Paste also copy formulas?
We have seen that copy or copy/paste method combination not only copy values but formatting as well.
Unlike simple Value property that only copies the value from cell or range of cells.
Question is, does it also copy formulas?
In the following example, we have a sheet (Sheet1) that contains the Excel VLOOKUP formula.
We will copy the complete range of cells with data and cell that contains VLOOKUP in Sheet2. So, let us see the result:
|
1 2 3 |
Sub copy_ex() Range("A1:E14").Copy Destination:=Worksheets("Sheet2").Range("A1:D10") End Sub |
Result:
So, yes it also copies formulas to the destination.
Conclusion:
On that basis, we can conclude that if you require to copy only values from the source range to the destination then use the Value property of the Range.
If you require to copy/paste contents including values, formatting, and formulas then use the Copy/Paste methods.
