Copy Rows to Another Sheet when a Criteria is Met

Hi all,

Essentially, I am trying to eliminate manual tracking of defects in numerous sheets. I want to copy rows from numerous sheets within a spreadsheet to another sheet when one of the fields = Fail.

For Example:
Sheet 1=Testing item A, within this sheet there are 11 Columns (stays the same) and 10 rows/tests. Column E (all the columns are the same throughout sheets) is used to identify if a test=Pass/Fail
Sheet 2=Testing item B, within this sheet there are 11 Columns (stays the same) and 5 rows/tests. Column E (all the columns are the same throughout sheets) is used to identify if a test=Pass/Fail
Sheet 3=Defects, this should contain all the rows from Sheet 1 & 2 where Column E=Fail. Therefore, if Sheet 1 has 5 items that failed, and Sheet 2 has 2 items that failed, Sheet 3 (Defect list) would show 7 items that failed.

Any help would be most appreciated. I've been looking around for a solution to my problem, the closest I got was with the following thread:
ThisWebsite/excel-formulas-and-functions/1256148-copy-complete-rows-based-on-criteria-of-cells.html

Basically I just create another Helper Sheet, link data on Sheet Item A and Item B

And sort in out... link the sort data into Defects sheet

I'm sure someone else will come with the best solution

I'm not using Index, Match or Indirect formula, because of personal preference

Hi biex,

That you so much for looking into it, this approach works with running only 15 tests. However, in my real spreadsheet I have 50+ sheets with 2000+ cumulative test scenarios, some sheets might have only 1 test scenario, other sheets would have 200+ test scenarios.Therefore, I am trying to write something up that would recognize Fail items and skip everything else.