Maco 2 Copy dynamic data from multiple worksheets into Summary worksheet in same workbook

Hi, hoping someone will be able to assist as I am new to creating scripts and it would save me a lot of copying and pasting if a macro can do it.
I need a macro that will copy specific data ranges from the worksheet "Asbestos Removal" through to the last worksheet in the Workbook "Working at Client's Workplace", and paste that data into a summary sheet in the same workbook.
I need the macro to copy A1 (risk name) and then A5 to I5 (Risk Activity, Pre and Post Controls) from each worksheet in the range and paste it into the next available blank row in the summary sheet.
The problem is that while the column range in each worksheet is static, the row range is dynamic and it has to stop and not include the data from the heading HOC Level and below.
I have attached the worksheet to give you the basic structure in the hope that someone can help

Give this a try

Please Login or Register  to view this content.

Heres your workbook

PRA v2.xls

1) I fixed the STEEL ERECTION sheet so that extra row 3 is merged back into row 2 like all the other sheets. The data now starts at row5 on all sheets.

2) I'd recommend a self-running macro. This macro is placed into the SUMMARY module and will run itself anytime you bring the sheet up onscreen, gathering up all the info each time.

Please Login or Register  to view this content.

@Jerry

Good catch...

If that takes care of your question, please select Thread Tools from the menu above and mark the thread as solved. Thanks.

Take out the "LETTERS" so your risk prioritizations are straight numeric, then you can simply SORT the data as a whole by that column.

@Jerry

Will it sort being that Columns B,C,D are merged?

Probably not. Add that to the myriad of reasons to not merge cells like this in a raw data.

@Annette

See this hls

Please Login or Register  to view this content.