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 Sheet1With Worksheets("Sheet1")
      LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
   End With'Find first row where values should be posted in Sheet2With 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 Sheet2For 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 IfEnd WithNext 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 Sheet1With Worksheets("Sheet1")
      LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
   End With'Find first row where values should be posted in Sheet2With 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 Sheet2For 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 IfEnd WithNext 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 .