Copy from base roster to personal roster (VBA)

[Phil Smith]

Hi Excel people.

I have a worksheet, "Base Roster", which contains my work base roster in numerical order from Week 1 in row 2 down to Week 80 in row 81.

I have a regular swap with a colleague, so have another worksheet ("My Roster") where one week I work my own line of work, the next week I work my colleagues line of work and vice versa. He works all the early shifts and i work all the lates.

At the moment my code loops through the base roster so C3:AD3 is then named as "Week1", through to C81:AD81 named as "Week80". The code then loops through each line of "My Roster", extracts the week numbers B2:B81 then copies and pastes the apporpriate range "Week1" etc, to each line.

What I would like to happen is that when each line of the base roster is filled and named, that week number is then searched for in "My Roster", which could appear more than once, and then the relevant week pasted to that relevant row.

Thanks in advance.

 

[DanteAmor]

What I would like to happen is that when each line of the base roster is filled and named

You could put generic examples of what you have on each sheet before it is filled and named.
And then the result you want after filling and naming.