[FONT="&][SIZE=3]Hi Excel gurus
My 2016 workbook requires the following Macros and also a suggestion on Data collation
I could not upload a file as the forum rule says but tried to copy the table but it is not copying the borders. Please let me know if it is not helpful
1) [/SIZE][/COLOR][COLOR=#222222][SIZE=3]MACRO in the ‘Task Allocation’ sheet[/SIZE][/COLOR]
[COLOR=#222222][FONT=Arial]Macro to copy therows from Column B to Column G and paste it to the individual ‘Team WiP’ sheetas per selection from the drop down list from column A in Sheet-‘TaskAllocation’.[/FONT][/COLOR]
[COLOR=#222222][FONT=Arial]The selection ofrows starts from row 6 only[/FONT][/COLOR]
[COLOR=#222222][FONT=Arial]The entries in the‘Task Allocation’ sheet will remain while it will be copied across to therelevant sheets as per selection from column A.[/FONT][/COLOR]
[COLOR=#222222][FONT=Arial]As an example, if Iselect ‘Team 1’ from the dropdown list, the entire data on that row from columnB to G to be copied in to ‘Team1 WiP’ sheet. If there are more allocation toTeam 1 from the Task allocation sheet, it would be copied to the next availablerow in the individual team sheet[/FONT][/COLOR]
[COLOR=#222222][FONT=Arial]
[TABLE="width: 1282"]
<tbody>[TR]
[TD][TABLE="width: 1282"]
<tbody>[TR]
[TD]Allocated to[/TD]
[TD] #[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Work Type[/TD]
[TD]Plan Expiry Date[/TD]
[TD]Date Allocated to Planner[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Team 1[/TD]
[TD]2354[/TD]
[TD]nmngh[/TD]
[TD]rtfgtr[/TD]
[TD]New Plan - Bilat (non ECEI, YPIRAC or Hospital)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Team 2[/TD]
[TD]2355[/TD]
[TD]nmngh[/TD]
[TD]rtfgtr[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2356[/TD]
[TD]nmngh[/TD]
[TD]rtfgtr[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/FONT]2. MACRO in each ‘Team WiP’ sheet
Once the workallocations are made in to the team sheet, the next step is to allocate thetask into individual team member.
Macro to copy therows from Column B to Column G and paste it to the individual ‘Team member’sheet as per selection from the drop-down list from column A in individual‘Team WiP Sheet’
There areapproximately 6 members in each team. I added 6 sheets for trail for now and Ihave to add another 34 more sheets in to this workbook.
As an example if Iselect ‘A’ from the dropdown list from column A in the ‘Team1 WiP’, the entiredata on that row from column B to G to be copied in to ‘Team member A’ sheet.Similarly when team member AA is selected in the ‘Team2 WiP’, the entire dataon that row from column B to G to be copied in to ‘Team member AA’ sheet.
If there are moreallocation to Team 1 from the Task WiP sheet, it would be copied to the nextavailable row in the individual team member sheet
[TABLE="width: 1773"]
<tbody>[TR]
[TD]Allocated to Planner[/TD]
[TD] #[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Work Type[/TD]
[TD]Plan Expiry Date[/TD]
[TD]Date Allocated to Planner[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]2354[/TD]
[TD]nmngh[/TD]
[TD]rtfgtr[/TD]
[TD]New Plan - Bilat (non ECEI, YPIRAC or Hospital)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
3.Textbox macro
In each individual ‘Team WiP’ sheets, I added a text box to create a copy of thatwork sheet. Requesting a macro to create a new sheet coping only the values(not any formulas) from Column A to S. Is there a way I can avoid the text boxbeing copied to the newly created sheet?
(I have a macrothat creates and copies the sheet but it is copying the formulas and the textbox into the new sheet as well)
4) KPI Data sheet
To analyse data on3 main criteria.
1. Region baseddata
2. Office baseddata
3. Team based data
Sub criteria 1.Monthly, Quarterly and Yearly
Sub criteria 2.Work Type (Column
Sub criteria 3.Appointments (Column I)
Sub criteria 2. Status(Column L)
For example, if mymanager asks me to give statistics (from all six teams or individual team) forthe month of December 2017 for office 1 on selected work type(s),I want somesuggestions for Data to be collated.
My plan is tocreate a separate sheet that will copy the contents from each team membersheet. Then I use the VLookup function to collate the data. Will that serve thepurpose?
Please share your thoughts
Regards malli007
My 2016 workbook requires the following Macros and also a suggestion on Data collation
I could not upload a file as the forum rule says but tried to copy the table but it is not copying the borders. Please let me know if it is not helpful
1) [/SIZE][/COLOR][COLOR=#222222][SIZE=3]MACRO in the ‘Task Allocation’ sheet[/SIZE][/COLOR]
[COLOR=#222222][FONT=Arial]Macro to copy therows from Column B to Column G and paste it to the individual ‘Team WiP’ sheetas per selection from the drop down list from column A in Sheet-‘TaskAllocation’.[/FONT][/COLOR]
[COLOR=#222222][FONT=Arial]The selection ofrows starts from row 6 only[/FONT][/COLOR]
[COLOR=#222222][FONT=Arial]The entries in the‘Task Allocation’ sheet will remain while it will be copied across to therelevant sheets as per selection from column A.[/FONT][/COLOR]
[COLOR=#222222][FONT=Arial]As an example, if Iselect ‘Team 1’ from the dropdown list, the entire data on that row from columnB to G to be copied in to ‘Team1 WiP’ sheet. If there are more allocation toTeam 1 from the Task allocation sheet, it would be copied to the next availablerow in the individual team sheet[/FONT][/COLOR]
[COLOR=#222222][FONT=Arial]
[TABLE="width: 1282"]
<tbody>[TR]
[TD][TABLE="width: 1282"]
<tbody>[TR]
[TD]Allocated to[/TD]
[TD] #[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Work Type[/TD]
[TD]Plan Expiry Date[/TD]
[TD]Date Allocated to Planner[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Team 1[/TD]
[TD]2354[/TD]
[TD]nmngh[/TD]
[TD]rtfgtr[/TD]
[TD]New Plan - Bilat (non ECEI, YPIRAC or Hospital)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Team 2[/TD]
[TD]2355[/TD]
[TD]nmngh[/TD]
[TD]rtfgtr[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2356[/TD]
[TD]nmngh[/TD]
[TD]rtfgtr[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/FONT]2. MACRO in each ‘Team WiP’ sheet
Once the workallocations are made in to the team sheet, the next step is to allocate thetask into individual team member.
Macro to copy therows from Column B to Column G and paste it to the individual ‘Team member’sheet as per selection from the drop-down list from column A in individual‘Team WiP Sheet’
There areapproximately 6 members in each team. I added 6 sheets for trail for now and Ihave to add another 34 more sheets in to this workbook.
As an example if Iselect ‘A’ from the dropdown list from column A in the ‘Team1 WiP’, the entiredata on that row from column B to G to be copied in to ‘Team member A’ sheet.Similarly when team member AA is selected in the ‘Team2 WiP’, the entire dataon that row from column B to G to be copied in to ‘Team member AA’ sheet.
If there are moreallocation to Team 1 from the Task WiP sheet, it would be copied to the nextavailable row in the individual team member sheet
[TABLE="width: 1773"]
<tbody>[TR]
[TD]Allocated to Planner[/TD]
[TD] #[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Work Type[/TD]
[TD]Plan Expiry Date[/TD]
[TD]Date Allocated to Planner[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]2354[/TD]
[TD]nmngh[/TD]
[TD]rtfgtr[/TD]
[TD]New Plan - Bilat (non ECEI, YPIRAC or Hospital)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
3.Textbox macro
In each individual ‘Team WiP’ sheets, I added a text box to create a copy of thatwork sheet. Requesting a macro to create a new sheet coping only the values(not any formulas) from Column A to S. Is there a way I can avoid the text boxbeing copied to the newly created sheet?
(I have a macrothat creates and copies the sheet but it is copying the formulas and the textbox into the new sheet as well)
4) KPI Data sheet
To analyse data on3 main criteria.
1. Region baseddata
2. Office baseddata
3. Team based data
Sub criteria 1.Monthly, Quarterly and Yearly
Sub criteria 2.Work Type (Column
Sub criteria 3.Appointments (Column I)
Sub criteria 2. Status(Column L)
For example, if mymanager asks me to give statistics (from all six teams or individual team) forthe month of December 2017 for office 1 on selected work type(s),I want somesuggestions for Data to be collated.
My plan is tocreate a separate sheet that will copy the contents from each team membersheet. Then I use the VLookup function to collate the data. Will that serve thepurpose?
Please share your thoughts
Regards malli007
Last edited:
