Pulling a selected summary to a destination sheet from multiple worksheets in the same workbook

[saifrusho]

Hey all!
Seriously struggling here. See if you can help this poor soul out!

What I am looking for:
As you can see below, there are 7 worksheets in this book, 5 of which have same column headings (second to sixth worksheets). I want to pull selected rows from these 5 worksheets into the 7th worksheet (CAP) while not disturbing the entries in any of the 5 worksheets (entries in these 5 worksheets should stay as is).

Here is the screenshot of the workbook. Any row in the five worksheets (A. LABOR, B. HEALTH & SAFETY, C. ENVIRONMENT, D. ETHICS, E. MANAGEMENT SYSTEM) with 'NO' selected in column D should go to CAP worksheet (the last one). But column D itself should not go. And every time I run the VBA, the process should repeat as fresh (not adding to the previously run VBA). I also want the exported rows in CAP worksheet to be 'wrapped' so that all texts show properly.

I saw a similar one posted here: https://www.mrexcel.com/forum/excel...ected-rows-multiple-sheets-summary-sheet.html
But 1) I could not quite apply it to all 5 of my needed worksheets and 2) if I run it twice, it adds to the previously run data instead of creating afresh.

Any help would be highly appreciated.

 

[FDibbins]

Am unable to view images (Im at work), but I can sorta picture what you want, except for - your heading seems to indicate pulling from a selected sheet, but your description seems to say pulling from multiple sheets?

Cant help with VBA, but maybe I can help with using a helper column on each data sheet, and then formulas to pull from each sheet?

 

[saifrusho]

Hi: I have added a new post with the image. Please see if this one makes better sense. Apologies - newbie here.
https://www.mrexcel.com/forum/test-...sheets-destination-worksheet.html#post4829757