VBA: How to Copy + Paste two rows by cells Cycled through a nested For Each Loop between Worksheets

Question

I'm trying to paste a row that I've copied earlier in another worksheet. This is my current code:

Sub Paste()
    Dim cellSearch As Range, rng As Range, prodID As Range, rngHdrFound As Range, ws As Worksheet, cell As Range
    Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    ws.Select
    Set rng = ThisWorkbook.Sheets(1).UsedRange
    Set rngHdrFound = ThisWorkbook.Sheets(2).UsedRange
    For Each cellSearch In rng
        For Each prodID In rngHdrFound
            'If the cell value is equal to the PROD ID value then paste the entire row to the new workbook
            If (prodID.Value = cellSeach.Value) Then
                For Each cell In ws.Columns(1).Cells.Address
                    If IsEmpty(cell) = True Then
                    ThisWorkbook.Sheets(1).prodID.Rows.Copy _
                    ThisWorkbook.ws.Range("cell").Rows
                    End If
                Next cell
            End If
        Next prodID
    Next cellSearch
End Sub

In this code I create a new worksheet, set ranges for my first two sheets with data in them, and then cycle through a ForEach loop in order to compare the data of the first two sheets together. When it finds a match, then it pastes the data in the new worksheet that we created at the beginning.

I'm not sure that I'm writing my copy + paste statements right, as I'm getting a "runtime error 1004: method range of object _worksheet failed" on my copy function. If anyone could help me get this code working, that would be fantastic. Thank you all for your help in advance!

Scott Craner Scott Craner · Accepted Answer · 2017-03-20T22:18:40

You want EntireRow not Rows and Cell is a variable range it should not be wrapped in Range():

                prodID.EntireRow.Copy cell

VBA: How to Copy + Paste two rows by cells Cycled through a nested For Each Loop between Worksheets

1 Answers