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

The Range object in VBA has a 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:

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 the B3 cell to the F3 cell by using the Copy method.

Code:

Sub copy_ex()

  Range("B3").Copy Destination:=Range("F3")

End Sub

Result:

Copying range of cells example

Now we will copy the B3 to B8 cells content to the F3 to F8 cells by using the 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 need to copy the value and not format then you may use the following two approaches as well to copy/paste:

The following code shows with output:

Sub copy_ex()

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

End Sub

Output:

Or more explicitly, use the .Value produces the same result:

Sub copy_ex()

  Range("F4").Value = Range("B4").Value

End Sub

It produces the same result as above.

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

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 a cell or range of cells.

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 cells that contain 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.