Method 1 – Copy Only Values to Destination with the PasteSpecial Method of Excel VBA
⧪ Step 1: Activating the Source Worksheet
The 1st step that we need to accomplish is to activate the source worksheet. The source worksheet is Sheet1. The line of code will be:
Worksheets("Sheet1").ActivateThis step isn’t mandatory if the source worksheet is active before running the code.
⧪ Step 2: Copying the Desired Range from the Activated Worksheet
Copy the desired range from the active worksheet. Copy the range B3:C13. The line of code will be:
ActiveSheet.Range("B3:D13").Copy⧪ Step 3: Activating the Destination Worksheet
Activate the destination worksheet. Here it’s Sheet2.
The line of code will be:
ActiveSheet.Range("B3:D13").CopyThis method isn’t mandatory if the source workbook is already open. The source workbooks and the destination workbook are the same.
⧪ Step 4: Pasting Only the Value with the xlPasteValues Property of the PasteSpecial Method
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
If you want, you may turn the CutCopyMode off after pasting the values.
ActiveSheet.Range("B3:D13").CopySo 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 Sheet1 to the range B3:D13 of Sheet2.
Method 2 – Copy Only Values to Destination by Iterating Through Two For-Loops in Excel VBA
⧪ Step 1: Starting an Iteration Equal to the Row Count of the Source Range
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
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
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
We have to end the loops.
Next j
Next iSo 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. Copy only the values from the range B3:D13 to Sheet1 to the range B3:D13 of Sheet2.
Examples Involving Copying Only Values to Destination with Excel VBA (Macro, UDF, and UserForm)
Method 1 – Developing a Macro to Copy only Values to a Destination Range with VBA in Excel (Using the PasteSpecial Method)
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:
Run the code, it copied only the values from the range B3:D13 to Sheet1 to the range B3:D13 of Sheet2.
Method 2 – Developing a User-Defined Function to Copy only Values to a Destination Range with VBA in Excel (Using the Iteration Method)
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)
Press CTRL + SHIFT + ENTER (Array Formula). Copy only the values of the range B3:D13 to the destination range.
Method 3 – Developing a UserForm to Copy Only Values to a Destination Range with VBA in Excel
⧪ Step 1: Inserting a New UserForm
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).
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
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
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
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
Insert a new Module and add this code to run 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 you want to copy and run the Macro called Run_UserForm.
The UserForm will load the Copy Only Values in the worksheet. Enter the required inputs there.
Copy the columns called Book Name and Price of the range B3:D13 of Sheet1 to the range B3 (First Cell) of Sheet3.
Inserted inputs like this.
Click on the button Copy. Only the values from the selected columns of your source range will be copied to the destination range.
Things to Remember
We used the xlPasteValues property of the PasteSpecial method of VBA. Other than the xlPasteValues property, there are 11 more properties of the PasteSpecial method.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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
- Copy and Paste Values to Next Empty Row with Excel VBA
- How to Autofilter and Copy Visible Rows with Excel VBA
