Hello:
(Please note I will add an example of the workbook tomorrow) if anyone feels they have an answer from my description, Id be very grateful for a response:
I have a workbook that is used as a calendar to manage a team of employees working across multiple contracts.
The workbook contains multiple sheets, which consist of:
Overview (summary sheet) this is what I need the VBA to populate.
Contract sheets (15 different contract sheets)
The overview (summary sheet) shows all the employees and indicates which contract each employee is working on, each day. It currently does this by a very long formula!, which looks over all the contract sheets (Which cannot be future proofed, for example if another contract sheet is added)
column A2:A25 (each row contains the name of the employee)
Columns B1:F1 contains the days Monday - Friday.
The contract sheets:
Each contract has its own sheet, the sheet is named as the contract.
The sheet comprises of the following information:
A2:A5 contains employee name (this is populated manually if the employee is working on that contract for anytime that week.
B1:F1 contains Monday-Friday.
The work content the employee is expected to complete is then input on the grid reference corresponding to the day and employee completing the task.
Therefore any one employee could be placed on any 15 contracts. For example employee 1 could be working on contract 5 on M, T & W and contract 8 on T & F.
Therefore this needs to refer back the the overview page to identify which employees are working on each contract.
The code Im after needs to be a VBA that does the following:
Identifies an employee named on a contract and has tasks listed on seperate days.
Then populate this onto the summary sheet, by stating which contract the employee is working on each day. Furthermore i need the VBA future proofed, for example if another contract sheet is added the code will look at the new sheet too, with the same set of rules.
I hope that makes sense and would be very grateful for some help with this. As I mentioned I can solve the issue with a formula, however it needs to be VBA, to future proof the rule and also prevent users from knocking the formula out by mistake.
(Please note I will add an example of the workbook tomorrow) if anyone feels they have an answer from my description, Id be very grateful for a response:
I have a workbook that is used as a calendar to manage a team of employees working across multiple contracts.
The workbook contains multiple sheets, which consist of:
Overview (summary sheet) this is what I need the VBA to populate.
Contract sheets (15 different contract sheets)
The overview (summary sheet) shows all the employees and indicates which contract each employee is working on, each day. It currently does this by a very long formula!, which looks over all the contract sheets (Which cannot be future proofed, for example if another contract sheet is added)
column A2:A25 (each row contains the name of the employee)
Columns B1:F1 contains the days Monday - Friday.
The contract sheets:
Each contract has its own sheet, the sheet is named as the contract.
The sheet comprises of the following information:
A2:A5 contains employee name (this is populated manually if the employee is working on that contract for anytime that week.
B1:F1 contains Monday-Friday.
The work content the employee is expected to complete is then input on the grid reference corresponding to the day and employee completing the task.
Therefore any one employee could be placed on any 15 contracts. For example employee 1 could be working on contract 5 on M, T & W and contract 8 on T & F.
Therefore this needs to refer back the the overview page to identify which employees are working on each contract.
The code Im after needs to be a VBA that does the following:
Identifies an employee named on a contract and has tasks listed on seperate days.
Then populate this onto the summary sheet, by stating which contract the employee is working on each day. Furthermore i need the VBA future proofed, for example if another contract sheet is added the code will look at the new sheet too, with the same set of rules.
I hope that makes sense and would be very grateful for some help with this. As I mentioned I can solve the issue with a formula, however it needs to be VBA, to future proof the rule and also prevent users from knocking the formula out by mistake.
