populate a list on one work sheet from multiple work sheets that meet a criteria

I need to automate the defect list to capture the Step Description (Column H), Actual Outcome (Column J) and Step Performed By (Column L) from multiple Test Cycle work sheets where the Status (Column E) of the test case = Failed (Codes!$F$6) and create a list in the Defect List that populates Summary ( Column B) with the Step Description , and Description (Column C) with the Actual Outcome and Detected By (Column D) with Step Performed By . I wish to avoid blank lines in the defect list and have a key stroke combination to manual initiate the update. Data is to be gathered from the following worksheets: System Test Case Cycle 1, System Test Case Cycle 2, Regression Test Case, UAT Test Case, UAT Test Cases - Cypress, UAT Test Cases - Five Hills, UAT Test Cases - Heartland, UAT Test Cases - Kelsey Trail, UAT Test Cases - MCR, UAT Test Cases - Prairie North, UAT Test Cases - PAP, UAT Test Cases - Regina, UAT Test Cases - Saskatoon, UAT Test Cases - Sun Country, and UAT Test Cases - Sunrise.