Table of Contents
Using VBA, you can copy rows from one sheet to another sheet based on criteria by looping through the source sheet, comparing the values in the row with the criteria, and then copying the rows that meet the criteria to the destination sheet. This can be done by using the Range.Copy method and a few If statements to check the criteria.
You can use the following syntax in VBA to copy each row in one sheet that meets specific criteria to another sheet:
Sub CopyToAnotherSheet()
Dim LastRow As Long
'Find last used row in a Column A of Sheet1
With Worksheets("Sheet1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
'Find first row where values should be posted in Sheet2
With Worksheets("Sheet2")
j = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
End With
'Paste each row that contains "Mavs" in column A of Sheet1 into Sheet2
For i = 1 To LastRow
With Worksheets("Sheet1")
If .Cells(i, 1).Value = "Mavs" Then
.Rows(i).Copy Destination:=Worksheets("Sheet2").Range("A" & j)
j = j + 1
End If
End With
Next i
End Sub
This particular macro will copy each row in Sheet1 where column A is equal to “Mavs” and paste each of these rows into the next available rows in Sheet2.
The following example shows how to use this syntax in practice.
Example: Copy Rows to Another Sheet Based on Criteria Using VBA
Suppose we have the following dataset in Sheet1 that contains information about various basketball players:
And suppose we have the following dataset in Sheet2:
Notice that Sheet2 only contains data for players on the Warriors team.
Suppose we would like to copy each row in Sheet1 where the Team column is equal to Mavs and paste each of these rows in the next available rows in Sheet2.
We can create the following macro to do so:
Sub CopyToAnotherSheet()
Dim LastRow As Long
'Find last used row in a Column A of Sheet1
With Worksheets("Sheet1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
'Find first row where values should be posted in Sheet2
With Worksheets("Sheet2")
j = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
End With
'Paste each row that contains "Mavs" in column A of Sheet1 into Sheet2
For i = 1 To LastRow
With Worksheets("Sheet1")
If .Cells(i, 1).Value = "Mavs" Then
.Rows(i).Copy Destination:=Worksheets("Sheet2").Range("A" & j)
j = j + 1
End If
End With
Next i
End Sub
When we run this macro, we receive the following output in Sheet2:
Notice that each row in Sheet1 where the Team name was equal to Mavs has been pasted into the next available rows in Sheet2.
Note: You can find the complete documentation for the VBA Copy method .