Help with VBA Array Code to copy data fron one Tab to another Tab

[VisionSmart]

I require some help with a tricky BA solution please which involves 'copying' existing data from one Tab and pasting into
another Tab in the same Workbook but into a specific cell, then add color.
The attached WorkBook contains examples and explains more.

Scenario
The attached Workbook has several Tabs as discussed below

Pre-Event Tab
Data in-Event tab is completed by Event Coordinators prior to an event using a blank template.

Referees Tab
3 x referees are appointed to every game; one officiates on-field at a time, while the other two are positioned on either sideline. All 3 referees interchange regularly to ensure they keep the game flowing.

Referee appointments are carried out by Coaching Panel – read on.

16 x Coaching Panel are identified by 16 x colored ID Codes. –- in this example.

H1

U1

Coaching Panel members are allocated to watch every game and monitor referee actions / decisions and record referees performance as part of education and training.
There can be instances where 2 or even 3 x Panel Members may be allocated to watch the same game. The first color and code is the primary Panel member. These Panel Member allocations are identified by the different color codes adjacent to each game in the Panel Draw Tab – Color Codes small box to left of each game (H1 U2, etc) adjacent to referee name.

Thanks to your website and you tube videos, I am using Offset and other functions to rapidly search through referee names for the correct one.
By – one click on cell C11, and follow the instructions.
Key in 01, or 02, or 03, or S1, S2, S3 -then one Left-click the handle (down arrow)- Next, select the name - One Left-click to accept

This example WB only has the first 2 x timeslots completed for testing purposes etc.


Panel Draw Tab
After the Event Coordinators have completed the Pre-Event data, the Workbook is then provided to the Coaching Panel members to decide which referees to appoint to which games.

This Tab is specifically for use of 16 x Coaching Panel people and provides allocation of fields / games they attend for each timeslot.
Which field they attend and when is identified by Code and Color discussed previously.

The appointments of referees process has these constraints
·****** At least one time-slot break between appointments.
·****** Maximum of 4 x appointments per referee per day.

Appointments D1 Tab – note Day 1 example data only
This is the Tab which requires some formula ‘interface’ from the Panel Draw Tab

This tab is designed to present the data in the Panel Draw Tab in a format which ensures Referees are appointed according to the above criteria.

The data contained in this Tab is the same as in the Panel Draw Tab but requires Field and Panel colour data populated against the appropriate Referee name.

Sequence Process – using the example WB
·****** Referees are appointed to games in Panel Draw Tab; Click on Tab C11 to read instructions.
This process ‘triggers’ the following formula in Appointments-D1 Tab
‘ =SUM(COUNTIF(INDIRECT({"'Panel Draw'!$C$11:$U$13"}),A5))
This count in C5 calculates the number of times referee is appointed to games in the same time slot.
(I plan to use Conditional formatting and colours if >1 presents in the same time-slot.
·****** Next while still in Panel Draw Tab; ,copy (H1) (copy retains the Cell format& colour) from C3 and is pasted into B11)
This is the part I believe requires VBA code.
The part I have not been able to figure out is VBA code to read the Field No Cell C8 in Panel Draw Tab, and paste that number with the corresponding H1 colour into Cell D5 in the Appointments-D1 Tab.
In this example, the data in Panel Draw Tab C8 = 01. I suspect Arrays are required here?
This data (01) needs to ‘post’ to Appointments-D1 Tab into cell D5. and use the same colour the primary coach code only e.g. 01.

Appreciate advices and assistance please.

[FDibbins]

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(note: this change is not optional )