Copy Paste in another worksheet based on cell value vba

[Penny110]

Hi, I'm new to VBA but did quite a bit of macros last week with the help from this forum. Now I'm stuck with this one and can't figure it out. Any help is much appreciated.

I have a schedule something like this on Sheet1:</SPAN>
[TABLE="width: 500"]
<TBODY>[TR]
[TD]Name</SPAN>
[/TD]
[TD]Mon</SPAN>
[/TD]
[TD]Tue</SPAN>
[/TD]
[TD]Wed</SPAN>
[/TD]
[/TR]
[TR]
[TD]John</SPAN>
[/TD]
[TD]Surrey 0900-1700</SPAN>
[/TD]
[TD]Van 1230-1945</SPAN>
[/TD]
[TD]APH 0745-1515</SPAN>
[/TD]
[/TR]
[TR]
[TD]Sue</SPAN>
[/TD]
[TD]Surrey 0715-1600</SPAN>
[/TD]
[TD]APH 0900-1700</SPAN>
[/TD]
[TD]APH 0900-1700</SPAN>
[/TD]
[/TR]
[TR]
[TD]Mike</SPAN>
[/TD]
[TD]APH 0715-1600</SPAN>
[/TD]
[TD]APH 0900-1700</SPAN>
[/TD]
[TD]APH 0745-1515</SPAN>
[/TD]
[/TR]
[TR]
[TD]Dan</SPAN>
[/TD]
[TD]APH 0900-1700</SPAN>
[/TD]
[TD][/TD]
[TD]Surrey 0715-1600</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

for each day of the week, shows the location and shift hours for each staff.
On Sheet2 = "Monday" I have the required shifts for each location on Mondays. something like this. I have 7 worksheets for 7 days of the week.</SPAN>
[TABLE="width: 500"]
<TBODY>[TR]
[TD]Mon</SPAN>
[/TD]
[TD][/TD]
[TD]Mon</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]APH 0715-1600</SPAN>
[/TD]
[TD][/TD]
[TD]Surrey 0715-1600</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]APH 0900-1700</SPAN>
[/TD]
[TD][/TD]
[TD]Surrey 0715-1600</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]APH 0900-1700</SPAN>
[/TD]
[TD][/TD]
[TD]Surrey 0900-1700</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]APH 0745-1515</SPAN>
[/TD]
[TD][/TD]
[TD]Surrey 0900-1700</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Surrey 0800-1730</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]
I need a macro to get the value of the first required shift for APH location (APH 0715-1600) and search in Sheet1 under Mon column, find the value, copy the corresponding staff name and paste it on Sheet "Monday" beside the first required shift on the left side cell. Then this repeats for the second required shift and so on. Then should go to the next location i.e. Surrey and starts with first required shift for Surrey (Surrey 0715-1600) and ...

The result should be like:</SPAN>


[TABLE="width: 500"]
<TBODY>[TR]
[TD]Mon</SPAN>
[/TD]
[TD][/TD]
[TD]Mon</SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mike</SPAN>
[/TD]
[TD]APH 0715-1600</SPAN>
[/TD]
[TD]Sue</SPAN>
[/TD]
[TD]Surrey 0715-1600</SPAN>
[/TD]
[/TR]
[TR]
[TD]Dan</SPAN>
[/TD]
[TD]APH 0900-1700</SPAN>
[/TD]
[TD][/TD]
[TD]Surrey 0715-1600</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]APH 0900-1700</SPAN>
[/TD]
[TD]John</SPAN>
[/TD]
[TD]Surrey 0900-1700</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]APH 0745-1515</SPAN>
[/TD]
[TD][/TD]
[TD]Surrey 0900-1700</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Surrey 0800-1730</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]


At the end I should know the required shifts that have no one assigned to and should be offered to staffs.

On Sheet1, not all staffs have an assigned shifts (some cells are blank). The cells are color coded by location.
On Sheet "Monday", there might be 2 or more of a same shift required.

Let me know if I need to provide more info.

Thanks in advance,</SPAN>

 

[Penny110]

Why I'm not getting any help