Search column in sheet to match data in another sheet issue

So here's what I'm trying to do. Sheet2 has column A that contains data. If the data in Sheet2/Column A matches Sheet1/Column D, then copy that entire row to Sheet2/Column E. When it pastes into column E, it has to be in the saw row that was searched for originally. I can't attach the spreadsheet because it contains data that I just cannot share. If you need any more information, please let me know. This has got me completely stumped.

sh1 = Sheets("Sheet1").Cells(Rows.Count, "D").End(xlUp).Row
sh2 = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("Sheet2").Select
 
    For i = sh2 To 2 Step -1
        For j = sh1 To 2 Step -1
            If Sheets("Sheet1").Range("D" & j).Value = Sheets("Sheet2").Range("A" & i).Value Then
                Sheets("Sheet1").Rows(j).Copy Destination:=Sheets("Sheet2").Range("E" & sh2 + 1)
                sh2 = Sheets("Sheet2").Cells(Row.Count, "A").End(xlUp).Row
            End If
        Next j
    Next i

AFAIK you can't then copy that entire row to Sheet2/Column E. Only to Column A

Originally Posted by JOHN H. DAVIS

AFAIK you can't then copy that entire row to Sheet2/Column E. Only to Column A

Is there a work around that you know of? I really don't need the whole row copied from Sheet1, I just need all the cells in the row that contain data to be copied over.

Or maybe:

Sub mattyp()

Sheets("Sheet1").Range(Cells(j, "D"), Cells(j, Sheets("Sheet1").UsedRange.Columns.Count)).Copy Destination:=Sheets("Sheet2").Range("E" & sh2 + 1)
End Sub

Originally Posted by JOHN H. DAVIS

Or maybe:

Sub mattyp()

Sheets("Sheet1").Range(Cells(j, "D"), Cells(j, Sheets("Sheet1").UsedRange.Columns.Count)).Copy Destination:=Sheets("Sheet2").Range("E" & sh2 + 1)
End Sub

I tried this but I'm getting a runtime 1004 error. "Application-defined or Object-defined error". It's pointing to the very first line of code - "sh1 = Sheets....." Any idea why it would throw that error?

You can copy a range:

Sub mattyp()

Sheets("Sheet1").Range(Cells(j, "D"), Cells(j, "YOUR LAST COLUMN TO COPY")).Copy Destination:=Sheets("Sheet2").Range("E" & sh2 + 1)
End Sub

Anyone have any ideas?

Do you have Dim statements for sh1 and sh2 in your code?

Yes, they are both setup as Long. This really has me stumped because it looks like it should work.

Okay, I made some headway. I commented out a line and now I can step through the code with no error messages. It ALMOST works. ahahhaha

What I mean is it will copy Sheet1/Row1 to Sheet2/Column E Row 10. But it won't copy any other rows over. Row 10 on Sheet 2 is where the last of the data is located, so it's started at the bottom of the data. This isn't a problem though as I really don't care if it starts at the top or bottom. I copied the code below exactly as it sits now.

Sub Test()
Dim sh1 As Long
Dim sh2 As Long
 
Sheets("Sheet2").Select
 
sh1 = Sheets("Sheet1").Cells(Rows.Count, "D").End(xlUp).Row
sh2 = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
 
 
    For i = sh2 To 2 Step -1
        For j = sh1 To 2 Step -1
            If Sheets("Sheet1").Range("D" & j).Value = Sheets("Sheet2").Range("A" & i).Value Then
                Sheets("Sheet1").Activate
                Sheets("Sheet1").Range(Cells(j, "A"), Cells(j, Sheets("Sheet1").UsedRange.Columns.Count)).Copy Destination:=Sheets("Sheet2").Range("E" & sh2)
                'sh2 = Sheets("Sheet2").Cells(Row.Count, "A").End(xlUp).Row
            End If
               
        Next j
    Next i
               
 
End Sub

Is there data in Sheet2 Column A?

Got it!!! I replaced the highlighted "sh2" with "i" and now the code runs through flawlessly. It runs from the bottom to the top. One last question though - How do I get the code to Start at Row 1 instead of at the bottom?

Sheets("Sheet1").Range(Cells(j, "A"), Cells(j, Sheets("Sheet1").UsedRange.Columns.Count)).Copy Destination:=Sheets("Sheet2").Range("E" & sh2)

Just invert it

    For i = sh2 To 2 Step -1
    For i = 2 To sh2 

Or j? Or both?

Just updating the i did exactly what I needed. Thanks again!!

You're welcome. Glad to help out.