Copy Paste in VBA

What is Copy and Paste in Excel VBA?

In VBA (Visual Basic for Applications), Copy and Paste in VBA code are methods used to move data or information from one location to another within an Excel workbook or other Microsoft Office applications. The Copy method is used to copy the selected data or information from a cell or range of cells by dynamic copy and paste in Excel VBA in a worksheet to the clipboard.

Consider an example where you want to copy the value of one cell to another. Using the rand() function, you generate a random value for cell A1.

As shown below, we use the copy-paste formula in Excel VBA to copy and paste the value from Cell A1 to Cell D4. The Paste method is used to paste the data or information stored in the clipboard to a specific location in the worksheet. It will result in the output for Copy Paste in VBA, as shown below.

• Output

How to Copy Paste in VBA?

To Copy Paste in VBA, you can use the Copy and Paste methods to move data or information from one location to another within a worksheet.

Examples

Let us look at some examples to understand the Copy-Paste process in Excel in detail.

Example #1 – Copy and Paste Values Using Range Object

Consider an example of copy paste in VBA where you want to copy a range of values from multiple cells and paste it into other cells. Here, numbers are randomly generated within 1-10 using the Excel function randbetween()

=RANDBTWEEN(1,10)

It prints these values in cells E1-E10.

• Step 1: Define a user-defined sub-procedure to copy and paste values in a range.

• Step 2: Define two variables, Source and Dest, of the datatype Range in VBA.

• Step 3: Assign the variables with the source to be copied from and the destination to be pasted into, using Source and Dest, respectively.

• Step 4: Copy the source values into the clipboard using the Copy function and paste it using the PasteSpecial function.

Code:

Sub CopyPasteWithRange()
Dim Source As Range
Dim Dest As Range
Set Source = Range(“E1:E10”)
Set Dest = Range(“G1:H1”)
Source.Copy
Dest.PasteSpecial xlPasteValues
End Sub

Here, xlPasteValues specifies the part of the range to be pasted, in this case, all the values.

• Step 5: Run the code using the Run button. The output is for Copy Paste in VBA, as shown below. Note since we’re using a random function, the source values will change every time the code is run.

Example #2 – Copy to Another Worksheet in the Same Workbook

Consider a table where you might need to copy values from one worksheet to another. We copy and paste the values into another sheet using the RANGE and VALUE functions. Assume the values are in “Example_2.1” in cells C1-C10.

• Step 1: Initialize a sub-procedure that copies and pastes values from one worksheet to another.

• Step 2: Declare two variables’ s’ and ‘d’ of the datatype Range.

• Step 3: Assign the source and destination columns to ‘s’ and ‘d,’ respectively.

• Step 4: To Copy Paste in VBA, equate the values to successfully copy and paste the table from column C in “Example_2.1” to column F in “Example_2”.

Code:

Sub CopyAndPasteBetweenSheets()
Dim s As Range
Dim d As Range
Set s = Worksheets(“Example_2.1”).Range(“C1:C10”)
Set d = Worksheets(“Example_2”).Range(“F1:F10”)
d.value = s.value
End Sub

• Step 5: Run the VBA code and print the output.

Example #3 – Copy from One workbook to Another Workbook

Consider a table whose values need to be copied from one workbook to another.

• Step 1: Create a sub-procedure to copy and paste values from one workbook into another.

• Step 2: Initialize two variables, s2, and d2, of the datatype Range.

• Step 3: Assign the source workbook to s2 and the destination workbook, where the values will be printed after being copied from the source to d2.

• Step 4: Equate both values so the copying process will occur.

Code:

Sub CopyAndPasteBetweenWorkbooks()
Dim s2 As Range
Dim d2 As Range
Set s2 = Workbooks(“Copy and Paste in VBA.xlsm”). Worksheets(“Example_3”).Range(“A1:A10”)
Set d2 = Workbooks(“Copy_paste.xlsm”).Worksheets(“Sheet1”).Range(“A1:A10”)
d2.value = s2.value
End Sub

• Step 5: After running the code, the table will be printed in cells A1-A10 in Workbook “Copy_paste.xlsm”.

Alternative Way for Using Copy-Paste in VBA

You can use the Value property to assign values from one range to another directly. Instead of using the Copy and Paste methods, we use the Value property of VBA to assign the values from the source to the destination directly. This method is faster and more efficient than using the clipboard to copy and paste data.

Using the Value property to assign values from one range to another directly, you can copy and paste data between different parts of your worksheet or even between other workbooks without using the clipboard.

Alternatively, you can also use Range.Copy property available in Excel VBA to perform copy paste in VBA.

Top Ways of VBA Copy and Paste as Values

1. Using ‘Copy’ and ‘Paste’ methods.

This method copies values from the source range to the clipboard using the Copy method and then pastes the values from the clipboard to the destination range using the PasteSpecial method.

2. Using the Value property

Using the Value property, this method directly assigns the values from the source range to the destination range. It is faster and more efficient than using the clipboard to copy and paste data.

3. Using the Range.Copy formula with destination argument:

This method uses the Copy method with a destination argument to copy the values from the source range to the destination range. This method is faster than using the clipboard to copy and paste data.

4. Using the Range.Value2 property.

This method uses the Value2 property of the Range object to directly assign the values from the source range to the destination range. This method is faster than using the Value property, but it may only work for some data types.

Important Things to Note

• To copy paste in VBA, use the Value property to directly assign values when possible, as it’s faster and more efficient than using the clipboard.
• You can also use the Destination argument with the Range.Copy method to paste values, as it’s faster than using the clipboard.
• Don’t use Select and Activate to copy and paste data, as it’s slow and inefficient.
• Don’t use xlPasteAll with PasteSpecial to paste all formats and formulas, as it’s slower than pasting only the values.
• Try not to use the clipboard unnecessarily, as it can cause conflicts with other applications and slow down your code. Like copy-paste, we can also use the PasteSpecial method to paste only specific attributes of the copied data, such as values, formats, or formulas to copy paste in VBA.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the Copy Paste in VBA, with its methods and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to Copy Paste in VBA. Here we explain top methods to copy-paste values in excel VBA, with examples & downloadable excel template. You can learn more from the following articles –

• Get Cell Value in Excel VBA
• VBA InStr
• VBA Application.Match