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
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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).
[No need to enter the full destination range, only the first cell B3 will do.]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: 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: Formula to Copy and Paste Values in Excel (5 Examples)
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 Apply VBA PasteSpecial and Keep Source Formatting in Excel
Similar Readings
- Copy and Paste in Excel and Keep Cell Size (7 Examples)
- How to Paste From Clipboard to Excel Using VBA
- Copy and Paste Exact Formatting in Excel(Quick 6 Methods)
- Exchange (Copy, Import, Export) Data Between Excel and Access
- How to Use Paste Special Command in Excel (5 Suitable Ways)
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.
=Copy_Only_Values(B3:D13)
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 Copy and Paste in Excel Using VBA (7 Methods)
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: How to Copy and Paste in Excel Without Changing the Format
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.
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. And don’t forget to visit our site ExcelDemy for more posts and updates.
Related Articles
- VBA Paste Special to Copy Values and Formats in Excel (9 Examples)
- Macro to Copy Data from One Workbook to Another Based on Criteria
- Excel Formula to Copy Cell value to Another Cell
- How to Copy the Same Value in Multiple Cells in Excel (4 Methods)
- Copy Data from One Cell to Another in Excel Automatically
- Excel VBA: Copy Cell Value and Paste to Another Cell
- How to Copy Multiple Cells to Another Sheet in Excel (9 Methods)