Copy specific data from Monthly auto generated workbook to master sheet

I am wondering if this is possible. I get this monthly report that has 5 tabs in it. The last tab, ALL_FAILURES_1mon, is a list of part and serial numbers that have failed that month. From that tab I copy the information into a master workbook that houses all the failures broken up by part number, each part number is a separate tab. I am wondering if there is a way to search in the "Monthly_Report" document for all rows containing the part number, 07X-000-ZZZ" and copy the entire row into the master fails list. I have attached a couple examples with sensitive information blocked out.

What I need is for when the macro is run, it will search "Monthly_Report.xlsx" ALL_FAILURES_1mon tab, for "07X-000-ZZZ" and copy all rows containing the part number and paste them in the next blank row of "Master_Fails_List" in appropriate tab.

Any help would be greatly appreciated.

*NOTE*I have attached both examples however my "Monthly_Report" document was too large so I had to upload it as a .xlsb but the original is .xlsx

Maybe:

Sub onbeillp111zzaa()
Dim i As Long
Dim ws As Worksheet
Dim wb2 As Workbook
Set ws = Workbooks("Monthly_Report.xlsb").Sheets("ALL_FAILURES_1mon")
Set wb2 = Workbooks("Master_Fails_List.xlsx")
ws.Activate
For i = 2 To Range("A" & Rows.count).End(3)(1).Row
    Rows(i).Copy wb2.Sheets(Range("A" & i).Value).Range("A" & Rows.count).End(3)(2)
Next i
End Sub

That worked on the example, when i tried to incorporate it into the actual file that has 1500 Rows and 21 part numbers, it gets a run time error, subscript out of range on the line below:

Rows(i).Copy wb2.Sheets(Range("A" & i).Value).Range("A" & Rows.Count).End(3)(2)

A subscript out of range normally means it can't find some object. It could be your wb2 variable or the Sheet name variable. Try changing this:

Rows(i).Copy wb2.Sheets(Range("A" & i).Value).Range("A" & Rows.Count).End(3)(2)

To this:

Rows(i).Copy wb2.Sheets(Range("A" & i).Text).Range("A" & Rows.Count).End(3)(2)

same error, does it matter that the new wb2 file has 21 tabs?

Is it possible to attach a file which error? Maybe duplicate it and replace sensitive data garble.

the actual files are too large, I could email them to you but i cannot attach them.

Did you try to attach them. I just need a sample which would cause the error. Maybe a copy and delete some of the data.

OK, I changed the files to .xlsb in order to attach but they are actually .xlsx files.

You were receiving the error because you sheet names changed too include other characters and you changed the name of the destination workbook as well. The code provided does not have that kind of flexibility.