Modifying existing code for more flexibility

[robbo1172]

Hi all

I have a workbook that works OK but I have found a way to make it more flexible and streamlined and I am hoping someone could help me out.

There are 5 WS at the front, MANUFACTURE, SITE, USE FOR PURPOSE and END OF LIFE.

WS 1 to 34 contain hazards and controls relating to 34 different hazard categories. I have added the columns K to O to WS 1 to WS34 as I would like to change the way this workbook operates (details on this below)

The DESIGN_EVALUATION WS has check boxes that if checked, relevant hazards from the different hazard categories are copied through to the first 5 WS.

For example, on the DESIGN_EVALUATION WS row 4, Item 1 Access and Egress, SITE, USE FOR PURPOSE, MAINTENANCE AND REPAIRS and END OF LIFE are ticked so all the hazards and controls on WS1 are copied to each of those 4 WS.

The problem is, the hazards may be different depending on where the work is carried out, e.g. workshop vs site.

What I would like to do is get rid of the DESIGN_EVALUATION WS and instead tick the boxes in columns K to O on WS1 to WS34 to determine what to copy to the first 5 WS.

For example, on WS1 item 1 is ticked in MANUFACTURE and SITE so will be copied to those WS only.

Similarly, item 2 is ticked in SITE and USE so it would be copied to those WS only.

I would like the Index No on the first 5 WS to operate as they do now, i.e. on MANUFACTURING WS it is prefixed with M then WS number (1 to 34) that the item came from and a suffix that increments for each item from the WS, e.g. M2.1 is
the first copied from WS2 to MANUFACTURING, M2.2 is the second, M2.3 is the third and M15.1 is the first from WS 15. Site has an S prefix etc. Hopefully this makes sense.

I have tried to work out the code behind this, but it is over my head, I am still working on VBA basics unfortunately.

Hopefully someone could help me out, thanks in advance for taking the time to read 

Anthony

[Logit]

.
For what it is worth .... I'm receiving an error message that the workbook is corrupt. Excel cannot recover / repair.

[robbo1172]

Thanks, works OK on my machine, not sure what is going on.

I'll have another look during the course of the day.

[robbo1172]

Hi all

I have tried to work this out but I'm stuck. I have re-uploaded in the hope that the workbook opens correctly.

Basically I just want to change the order of the process.

At the moment it checks the DESIGN_EVALUATION WS to determine what WS (WS1 to WS34) are required to be copied to the first 5 WS (MANUFACTURE, SITE, USE, MAINTENANCE, END OF LIFE).

I would instead like it to go through each WS (WS1 to WS34) and determine if the line item is required to be copied to the first 5 WS (MANUFACTURE, SITE, USE, MAINTENANCE, END OF LIFE).

If anyone can help it would be greatly appreciated. Thanks in advance.

Thanks