Excel VBA to Copy Only Values to Destination (Macro, UDF, and UserForm)

While working with VBA in Excel, we often need to copy only values from a range of cells to a destination range. In this article, I’ll show you how you can copy only values from a range of cells to a destination range through step-by-step analysis including examples involving Macro, User-Defined function, and UserForm with proper illustrations.

Copy Only Values to Destination with Excel VBA (Quick View)

Sub Copy_Only_Values_to_Destination_1()
Worksheets("Sheet1").Activate
ActiveSheet.Range("B3:D13").Copy
Worksheets("Sheet2").Activate
Range("B3").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

How to Copy Only Values to Destination with Excel VBA: Step by Step Analysis

Without further delay, let’s move to our main discussion. Our purpose is to copy only values from the range B3:D13 of Sheet1 to the range B3:D13 of Sheet2.

We can accomplish this in 2 ways.

Method 1: Copy Only Values to Destination with the PasteSpecial Method of Excel VBA

The first method involves the PasteSpecial method of VBA. I am showing you the step-by-step procedure to execute this.

⧪ Step 1: Activating the Source Worksheet

The 1st step that we need to accomplish is to activate the source worksheet. Here the source worksheet is Sheet1. The line of code will be:

Worksheets("Sheet1").Activate

This step isn’t mandatory if the source worksheet is already active before running the code.

⧪ Step 2: Copying the Desired Range from the Activated Worksheet

Then we have to copy the desired range from the active worksheet. Here we’ll copy the range B3:C13. The line of code will be:

ActiveSheet.Range("B3:D13").Copy

⧪ Step 3: Activating the Destination Worksheet

Next, we have to activate the destination worksheet. Here it’s Sheet2.

The line of code will be:

ActiveSheet.Range("B3:D13").Copy

Again, this method isn’t mandatory if the source workbook is already open. That is, the source workbooks and the destination workbook is the same.

⧪ Step 4: Pasting Only the Value with the xlPasteValues Property of the PasteSpecial Method

Finally, we have to paste only the values with the xlPasteValues property of VBA in the destination range (B3:D13 here).

The line of code will be:

ActiveSheet.Range("B3:D13").Copy

⧪ Step 5 (Optional): Turning off the CutCopyMode

This is optional. If you want, you may turn the CutCopyMode off after pasting the values.

ActiveSheet.Range("B3:D13").Copy

So the complete VBA code will be:

⧭ VBA Code:

Sub Copy_Only_Values_to_Destination_1()
Worksheets("Sheet1").Activate
ActiveSheet.Range("B3:D13").Copy
Worksheets("Sheet2").Activate
Range("B3").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

⧭ Output:

Run the code. It’ll copy only the values from the range B3:D13 to of Sheet1 to the range B3:D13 of Sheet2.

Read More: How to Use VBA to Paste Values Only with No Formatting in Excel

Method 2: Copy Only Values to Destination by Iterating Through Two For-Loops in Excel VBA

The PasteSpecial method described above may take a bit long time if we have a large data set to work with. In those cases, you can simply iterate through two for-loops to copy only the values from a certain range to another range.

Though this method seems a bit more complex than the previous one, for large data sets, this will be more suitable.

We’ll accomplish the same task as earlier, will copy the range B3:D13 of Sheet1 to the range B3:D13 of Sheet2.

⧪ Step 1: Starting an Iteration Equal to the Row Count of the Source Range

First, you have to start an iteration equal to the row count of the source range.

For i = 1 To Worksheets("Sheet1").Range("B3:D13").Rows.Count

⧪ Step 2: Starting an Iteration Equal to the Column Count of the Source Range

Next, we have to start alliteration equal to the column count of the source range.

For j = 1 To Worksheets("Sheet1").Range("B3:D13").Columns.Count

⧪ Step 3: Assigning the Value of Each Cell of the Source Range to that of the Destination Range

This is the most important job. We’ve to assign the value of each cell of the source range to that of the destination range.

Worksheets("Sheet2").Range("B3").Cells(i, j).Value = Worksheets("Sheet1").Range("B3:D13").Cells(i, j).Value

⧪ Step 4: Ending the Loops

Finally, we have to end the loops.

Next j
Next i

So the complete VBA code will be:

⧭ VBA Code:

Sub Copy_Only_Values_to_Destination_2()

For i = 1 To Worksheets("Sheet1").Range("B3:D13").Rows.Count
    For j = 1 To Worksheets("Sheet1").Range("B3:D13").Columns.Count
        Worksheets("Sheet2").Range("B3").Cells(i, j).Value = Worksheets("Sheet1").Range("B3:D13").Cells(i, j).Value
    Next j
Next i

End Sub

⧭ Output:

Run the code. It’ll again copy only the values from the range B3:D13 to of Sheet1 to the range B3:D13 of Sheet2.

Read More: How to Paste From Clipboard to Excel Using VBA

Examples Involving Copying Only Values to Destination with Excel VBA (Macro, UDF, and UserForm)

We’ve learned how to copy only the values from a source range to a destination range with VBA. Now let’s explore a few examples involving this.

Example 1: Developing a Macro to Copy only Values to a Destination Range with VBA in Excel (Using the PasteSpecial Method)

First of all, we’ll develop a Macro to copy only the values from a source range to a destination range. Actually, we’ve developed it already.

The VBA code to copy only the values of the range B3:D13 of Sheet1 to that of Sheet2 was:

⧭ VBA Code:

Sub Copy_Only_Values_to_Destination_1()
Worksheets("Sheet1").Activate
ActiveSheet.Range("B3:D13").Copy
Worksheets("Sheet2").Activate
Range("B3").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

⧭ Output:

When we run the code, it copied only the values from the range B3:D13 to of Sheet1 to the range B3:D13 of Sheet2.

Read More: How to Use VBA PasteSpecial and Keep Source Formatting in Excel

Example 2: Developing a User-Defined Function to Copy only Values to a Destination Range with VBA in Excel (Using the Iteration Method)

Now we’ll create a User-Defined function to copy only the values from one range to another range. We’ll use the iteration method here.

The VBA code will be:

⧭ VBA Code:

Function Copy_Only_Values(Rng As Range)

Dim Output() As Variant
ReDim Output(Rng.Rows.Count - 1, Rng.Columns.Count - 1)

For i = 1 To Rng.Rows.Count '
    For j = 1 To Rng.Columns.Count
        Output(i - 1, j - 1) = Rng.Cells(i, j)
    Next j
Next i

Copy_Only_Values = Output

End Function

⧭ Output:

Select the destination range and enter this formula in the first cell of the range.

Then press CTRL + SHIFT + ENTER (Array Formula). It’ll copy only the values of the range B3:D13 to the destination range.

Read More: How to Use VBA PasteSpecial for Formulas and Formats in Excel

Example 3: Developing a UserForm to Copy Only Values to a Destination Range with VBA in Excel

Finally, we’ll develop a UserForm to copy only the values to a destination range with VBA. I’m showing you the step-by-step procedure.

⧪ Step 1: Inserting a New UserForm

First of all, go to the Insert > UserForm button of the Visual Basic Editor to insert a new UserForm.

⧪ Step 2: Dragging the Necessary Tools

A UserForm called UserForm1 will be created with a Toolbox called Control. Drag 7 Labels (Label1, Label2, Label3, Label4, Label5, Label6, and Label7), 3 ListBoxes (ListBox1, ListBox2, and ListBox3), 2 TextBoxes (TextBox1 and TextBox2), and 1 CommandButton (CommandButton1) to the UserForm.

Change the displays of the Labels to Copy From, Worksheet, Range, Columns, Paste To, Worksheet, and Range (First Cell) respectively (as shown in the image).

Also, change the display of the CommandButton1 to Copy.

⧪ Step 3: Inserting Code for ListBox1

Double click on ListBox1. A Private Subprocedure called ListBox1_Click will open. Insert the following code there.

Private Sub ListBox1_Click()

On Error GoTo Solution1

For i = 0 To UserForm1.ListBox1.ListCount - 1
    If UserForm1.ListBox1.Selected(i) = True Then
        Worksheets(UserForm1.ListBox1.List(i)).Activate
        ActiveSheet.Range(UserForm1.TextBox1.Text).Select
        Exit For
    End If
Next i

UserForm1.ListBox2.Clear

For j = 1 To Selection.Columns.Count
    UserForm1.ListBox2.AddItem Selection.Cells(1, j)
Next j

Solution1:
    x = 21

End Sub

⧪ Step 4: Inserting Code for ListBox3

Then double-click on ListBox3. Another Private Subprocedure called ListBox3_Click will open. Insert the following code there.

Private Sub ListBox3_Click()

On Error GoTo Solution2

For i = 0 To UserForm1.ListBox3.ListCount - 1
    If UserForm1.ListBox3.Selected(i) = True Then
        Worksheets(UserForm1.ListBox3.List(i)).Activate
        If UserForm1.TextBox2.Text = "" Then
            ActiveSheet.Range(UserForm1.TextBox1.Text).Select
        Else
            ActiveSheet.Range(UserForm1.TextBox2.Text).Select
        Exit For
        End If
    End If
Next i

Solution2:
    x = 21

End Sub

⧪ Step 5: Inserting Code for TextBox1

Again double click on TextBox1. A Private Subprocedure called TextBox1_Change will open. Insert this code there:

Private Sub TextBox1_Change()

On Error GoTo Solution3

For i = 0 To UserForm1.ListBox1.ListCount - 1
    If UserForm1.ListBox1.Selected(i) = True Then
        Worksheets(UserForm1.ListBox1.List(i)).Activate
        ActiveSheet.Range(UserForm1.TextBox1.Text).Select
        Exit For
    End If
Next i

UserForm1.ListBox2.Clear

For j = 1 To Selection.Columns.Count
    UserForm1.ListBox2.AddItem Selection.Cells(1, j)
Next j

Solution3:
    x = 21

End Sub

⧪ Step 6: Inserting Code for TextBox2

Similarly, insert this code for TextBox2.

Private Sub TextBox2_Change()

On Error GoTo Solution4

For i = 0 To UserForm1.ListBox3.ListCount - 1
    If UserForm1.ListBox3.Selected(i) = True Then
        Worksheets(UserForm1.ListBox3.List(i)).Activate
        ActiveSheet.Range(UserForm1.TextBox2.Text).Select
        Exit For
    End If
Next i

Solution4:
    x = 21

End Sub

⧪ Step 7: Inserting Code for CommandButton1

Add this code for the CommandButton1 (CommandButton1_Click).

Private Sub CommandButton1_Click()

Copy_From = ""

For i = 0 To UserForm1.ListBox1.ListCount - 1
    If UserForm1.ListBox1.Selected(i) = True Then
        Copy_From = UserForm1.ListBox1.List(i)
        Exit For
    End If
Next i

If Copy_From = "" Then
    MsgBox "Select a Worksheet to Copy From. ", vbExclamation
    Exit Sub
End If

Copy_To = ""

For i = 0 To UserForm1.ListBox3.ListCount - 1
    If UserForm1.ListBox3.Selected(i) = True Then
        Copy_To = UserForm1.ListBox3.List(i)
        Exit For
    End If
Next i

If Copy_To = "" Then
    MsgBox "Select a Worksheet to Copy To. ", vbExclamation
    Exit Sub
End If

If UserForm1.TextBox1.Text <> "" Then
    Set Copy_Range = Worksheets(Copy_From).Range(UserForm1.TextBox1.Text)
Else
    MsgBox "Enter a Valid Range to Copy From. ", vbExclamation
    Exit Sub
End If

If UserForm1.TextBox2.Text <> "" Then
    Set Paste_Range = Worksheets(Copy_To).Range(UserForm1.TextBox2.Text)
Else
    MsgBox "Enter a Valid Range to Paste To. ", vbExclamation
    Exit Sub
End If

Copied = 0

For i = 0 To UserForm1.ListBox2.ListCount - 1
    If UserForm1.ListBox2.Selected(i) = True Then
        Worksheets(Copy_From).Activate
        Copy_Range.Range(Cells(1, i + 1), Cells(Copy_Range.Rows.Count, i + 1)).Copy
        Copied = Copied + 1
        Worksheets(Copy_To).Activate
        Paste_Range.Range(Cells(1, Copied), Cells(Copy_Range.Rows.Count, Copied)).PasteSpecial Paste:=xlPasteValues
    End If
Next i

Application.CutCopyMode = False

End Sub

⧪ Step 8: Inserting Code for Running the UserForm

Finally, insert a new Module and add this code for running the UserForm.

Sub Run_UserForm()

UserForm1.Caption = "Copy Only Values"
UserForm1.ListBox1.ListStyle = fmListStyleOption
UserForm1.ListBox1.BorderStyle = fmBorderStyleSingle

For i = 1 To Sheets.Count
    UserForm1.ListBox1.AddItem Sheets(i).Name
Next i

For i = 0 To UserForm1.ListBox1.ListCount - 1
    If ActiveSheet.Name = UserForm1.ListBox1.List(i) Then
        UserForm1.ListBox1.Selected(i) = True
        Exit For
    End If
Next i

UserForm1.TextBox1.Text = Selection.Address
UserForm1.ListBox2.ListStyle = fmListStyleOption
UserForm1.ListBox2.BorderStyle = fmBorderStyleSingle
UserForm1.ListBox2.MultiSelect = fmMultiSelectMulti
UserForm1.ListBox2.Clear

For j = 1 To Selection.Columns.Count
    UserForm1.ListBox2.AddItem Selection.Cells(1, j)
Next j

UserForm1.ListBox3.ListStyle = fmListStyleOption
UserForm1.ListBox3.BorderStyle = fmBorderStyleSingle

For i = 1 To Sheets.Count
    UserForm1.ListBox3.AddItem Sheets(i).Name
Next i

Load UserForm1
UserForm1.Show

End Sub

⧪ Step 9: Running the UserForm (The Final Output)

Your UserForm is now ready to use. Select the range of cells that you want to copy and run the Macro called Run_UserForm.

The UserForm will load in the worksheet with the Copy Only Values. Enter the required inputs there.

I want to copy the columns called Book Name and Price of the range B3:D13 of Sheet1 to the range B3 (First Cell) of Sheet3.

So I have inserted inputs like this.

Then click on the button Copy. Only the values from the selected columns of your source range will be copied to the destination range.

Read More: VBA Paste Special to Copy Values and Formats in Excel

Things to Remember

Here we’ve used the xlPasteValues property of the PasteSpecial method of VBA. Other than the xlPasteValues property, there are 11 more properties of the PasteSpecial method. Click to know them in detail.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

So this is these are the ways to copy only values to a destination range from a source range with VBA in Excel. Hope the examples made everything pretty clear for you. Still, if you have any questions, feel free to ask us.

Related Articles

• How to Copy Visible Cells Only without Header Using VBA
• Excel VBA: Copy Row If Cell Value Matches
• Excel VBA: Copy Cell Value and Paste to Another Cell
• How to Copy Multiple Rows in Excel Using Macro
• Copy and Paste Values to Next Empty Row with Excel VBA
• How to Autofilter and Copy Visible Rows with Excel VBA