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:
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
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
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


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,