Prantick Bala
Coping data, based on criteria, from one sheet to another is one of the frequently done tasks in Excel. If you have a lot of rows and want to copy them on another sheet based on some criteria it can be quite tiresome and time-consuming. But with Microsoft Visual Basic Application (VBA) you can create Macros with which you can easily copy data from one sheet to another based on different criteria. In this article, I’ll show you how you can copy rows to another worksheet based on 2 different types of criteria by using Excel VBA.
Let’s say, you have the following dataset in a worksheet named “Data” where sales and sales areas of different salesman are given. Now, you want to copy specific rows based on some criteria in another sheet.
Download Practice Workbook
2 Ways to Copy Rows to Another Worksheet Based on Criteria by Excel VBA
1. Copy Rows to Another Worksheet Based on Text Criteria
In this demonstration, I’ll show you how you copy rows from one worksheet to another based on text criteria. Suppose, you want to copy the data of the salesmen who sell in Virginia in a worksheet named Area Sales using Excel VBA. To do that, first,
➤ Press ALT+F11 to open the VBA window.
In the VBA window,
➤ Click on the Insert tab and select Module.
It will open the Module(Code) window. Now,
➤ Insert the following code in the Module(Code) window,
Sub Copy_Criteria_Text()
Application.ScreenUpdating = False
With ActiveSheet
.AutoFilterMode = False
With Range("C1", Range("C" & Rows.Count).End(xlUp))
.AutoFilter 1, "Virginia"
On Error Resume Next
.Offset(1).EntireRow.Copy Sheet3.Range("A" & Rows.Count).End(xlUp).Offset(1)
End With
.AutoFilterMode = False
End With
Application.ScreenUpdating = True
Sheet3.Select
End SubThe code will create a Macro named Copy_Criteria_Text that will search for Virginia in column C of the current worksheet and return the rows which contain Virginia in the worksheet named Area Sales (Sheet3).
After that,
➤ Close or minimize the VBA window.
➤ Press ALT+F8
It will open the Macro window.
➤ Select Copy_Criteria_Text in the Macro name box and click on Run.
As a result, the rows with Virginia will be copied in the worksheet named Area Sales
Read More: How to Copy Rows Automatically in Excel to Another Sheet (4 Methods)
2. Copy Rows to Another Worksheet Based on Number Criteria
Now, I’ll show you how you can copy rows from one worksheet to another worksheet based on number criteria. Suppose, you want to copy the data of the sales which is greater than $100000 to a worksheet named Top Sales. To do that, first,
➤ Press ALT+F11 to open the VBA window.
In the VBA window,
➤ Click on the Insert tab and select Module.
It will open the Module(Code) window. Now,
➤ Insert the following code in the Module(Code) window,
Sub Copy_Criteria_Number()
Application.ScreenUpdating = False
With ActiveSheet
.AutoFilterMode = False
With Range("D1", Range("D" & Rows.Count).End(xlUp))
.AutoFilter 1, ">100000"
On Error Resume Next
.Offset(1).EntireRow.Copy Sheet4.Range("A" & Rows.Count).End(xlUp).Offset(1)
End With
.AutoFilterMode = False
End With
Application.ScreenUpdating = True
Sheet4.Select
End SubThe code will create a Macro named Copy_Criteria_Number that will search for values greater than 100000 in column D of the worksheet named Data and return the rows which contain sales values more than $100000 in the worksheet named Top Sales (Sheet4).
After that,
➤ Close or minimize the VBA window.
➤ Press ALT+F8
It will open the Macro window.
➤ Select Copy_Criteria_Number in the Macro name box and click on Run.
As a result, the rows which contain sales values of more than $100000 will be copied into the Top Sales worksheet.
Read More: How to Copy Multiple Rows in Excel Using Macro (4 Examples)
Conclusion
With the two methods of this article, you’ll be able to copy rows from one worksheet to another worksheet based on different criteria by using Excel VBA. You can use the first method for text criteria and the second method for number criteria. If you have any type of confusion about any of the methods please feel free to leave a comment.
Related Articles
- How to Copy Multiple Cells to Another Sheet in Excel (9 Methods)
- Difference Between Paste and Paste Special in Excel
- How to Copy Alternate Rows in Excel (4 Ways)
- Excel VBA to Copy Data from Another Workbook without Opening
- VBA Paste Special to Copy Values and Formats in Excel (9 Examples)
- Apply VBA PasteSpecial and Keep Source Formatting in Excel
- How to Copy and Paste in Excel and Keep Cell Size (7 Examples)
I understand the demonstration. However, my question is to take the information from the master sheet in a workbook and place it on another sheet with the same catagory within the same workbook. How can that be done?
Hello, Anita Sessa!
Do you want to get the same information from a worksheet to another worksheet? If is that so, you can check this Link:
https://www.exceldemy.com/extract-data-from-one-sheet-to-another-in-excel-using-vba/
There are three examples to get the same information from one sheet to another.
Awesome. How would this be modified to paste the row into a new row of an existing table on another worksheet?
Thanks SARAH for your comment and appreciation.
You can use the following VBA code with necessary changes to apply your code.
Sub AddRowFromAnotherSheet()
Sheets(“sheet1”).Range(“B7:E7”).Copy Sheets(“sheet3”).Range(“B9:E9”)
End Sub