Multiple Worksheet_Change macros in one worksheet

M

malli007

New Member
Joined
Oct 28, 2015
Messages
2
[FONT="&amp][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
 
Last edited:

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
You must log in or register to reply here.

Similar threads

P
  • Question Question
Moving rows to different sheets based on drop down.
2
Replies
12
Views
1K
purevega
P
ChrisMac1
  • Question Question
VBA - Compare specific data in one column on one sheet, with the same column on 4 other sheets, copy specific cells if match found.
Replies
1
Views
438
ChrisMac1
ChrisMac1
Q
  • Question Question
Macro needed to insert 4 rows in table, copy data from above, copy data from another sheet and paste special (transpose values) to those new rows...re
Replies
1
Views
402
sijpie
sijpie
F
  • Solved
Copy paste from one Excel workbook to another with same sheet names and update dates in range
Replies
8
Views
548
Skiier89
S
B
  • Question Question
VBA Copying data from sheet 1 to sheet 2 not including duplicates
2
Replies
10
Views
987
Bobsta_666
B

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top