3 Ways of Copy/Paste Value, Formatting, Formulas in VBA / Excel

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:

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:

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:

Sub copy_ex()

  Range("F4") = Range("B4")

End Sub

Output:

Or more explicitly use .Value produces the same result:

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

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:

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.

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.

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:

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:

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.