Copy Paste in another worksheet based on cell value vba

[Penny110]

Hi, I'm new with 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:

Name	Mon	Tue	Wed
John	Surrey 0900-1700	Van 1230-1945	APH 0745-1515
Sue	Surrey 0715-1600	APH 0900-1700	APH 0900-1700
Mike	APH 0715-1600	APH 0900-1700	APH 0745-1515
Dan	APH 0900-1700		Surrey 0715-1600

<tbody>
</tbody>

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.

Mon		Mon
	APH 0715-1600		Surrey 0715-1600
	APH 0900-1700		Surrey 0715-1600
	APH 0900-1700		Surrey 0900-1700
	APH 0745-1515		Surrey 0900-1700
			Surrey 0800-1730

<tbody>
</tbody>

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:

Mon		Mon
Mike	APH 0715-1600	Sue	Surrey 0715-1600
Dan	APH 0900-1700		Surrey 0715-1600
	APH 0900-1700	John	Surrey 0900-1700
	APH 0745-1515		Surrey 0900-1700
			Surrey 0800-1730

<tbody>
</tbody>

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,