Compare list of values to rows in another sheet, copy row on first match

[RileyMartin]

Hi, I'm new to Excel VBA and struggling with syntax and the correct functions to use. I've tried different approaches but keep running into errors. Listed below is my latest attempt.

What I am trying to do is to compare a list from one sheet to the data on another sheet, row by row, cell by cell, and when a match is made, copy that entire row to a target sheet, break out of the for loop that compares the value against the cells in the current row, and goto the next row and start the cell by cell comparison again. Any help would be greatly appreciated. Thank you.

Option Explicit 'Added by SamT

Sub CopyRowsWithMatchingData()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim i, D, R, C 'Aded by SamT To satisfy Option Explicit

Set ws1 = Worksheets("DATA")
Set ws2 = Worksheets("LIST")

For D = 2 To 100 '(This is the loop to go through my list of values to compare)
  For R = 1 To 4091 '(This will be the row reference for the rows on the DATA sheet to loop through)
    For C = 1 To 2471 '(This will be the column reference for the cells in the current row to compare against)
      If ws2.Cells("A", D).Value = ws1.Cells(R, C).Value Then '(Compare value from LIST sheet against the current cell in the current row on the DATA sheet)
        ActiveWorkbook.Sheets("DATA").Rows(R).EntireRow.Copy
        ActiveWorkbook.Sheets("TARGET").Activate
        ActiveWorkbook.Sheets("TARGET").Rows(i).EntireRow.Select
        ActiveWorkbook.Sheets("TARGET").Paste
        Exit For '(At the very first match I copied the entire row to the TARGET sheet and then exit the loop and stop compare cells in that column)
      End If
    Next C
  Next R
Next D
End Sub

[SamT]

Target Rows(i)...Where is i assigned its value? That is the only problem I see.

Looping from 1 to some Magic Number is very bad programming practice. For one thing, every time an empty cell matches an empty cell, you'll be copying an empty Row to Target

LastRow = Cells(Rows.Count, "Column_Letter).End(xlUp).Row
LastColumn = Cells(Row_Number, Columns.Count).End(xlToLeft).Column

NextRow, (For Sheet Target,) =LastRow + 1

Using Find is a faster way than looping thru an entire Row
Advanced coding techniques:

Dim Cel As Range
Dim Found As Range

For each Cel in Range(Cells(2, "A"), Cell(Rows.Count, "A").End(xlUp)) 'Just the used part of "A"
   With Sheets("DATA")
      Set Found = .Cells.Find(Cel)' Will find the first match. Set Find to "By Columns" or "By Rows," I fergit which
      If not Found is Nothing then _
          'Space+Underscore is Line continuation mark
          .Rows(Cel.Row).Copy Sheets("Target").Cells(Rows.Count, "A").End(xlUp).Offset(1,0) 
              'Offset(1,) is down one cell from the bottom used cell
End With
Next Cel