Method 1 – Copy Rows To Another Worksheet Based on Text Criteria
➤ Press ALT+F11 to open the VBA window.
In the VBA window,
➤ Click 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 searches for Virginia in column C of the current worksheet and returns the rows that contain Virginia in the worksheet 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.
The rows with Virginia will be copied in the worksheet named Area Sales
Method 2 – Copy Rows to Another Worksheet Based on Number Criteria
➤ Press ALT+F11 to open the VBA window.
In the VBA window,
➤ Click 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 that 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.
The rows that contain sales values of more than $100000 will be copied into the Top Sales worksheet.
Download Practice Workbook
Related Articles
- How to Use Excel VBA to Copy Range to Another Excel Sheet
- Macro to Copy Specific Columns from One Worksheet to Another in Excel
- Excel VBA: Copy Range to Another Workbook
- How to Open Another Workbook and Copy Data with Excel VBA
- Excel VBA to Copy Data from Another Workbook without Opening
- Excel VBA to Loop Through Files in Folder and Copy Data
- How to Open All Excel Files in a Folder and Copy Data Using VBA
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