Hi, Hope you are all well. You will see below two images which represent 2 separate workbooks. The first is the Source workbook and the second is the Destination workbook. What I need help in doing is producing VBA code that will copy the relevant information from the Source workbook into the Destination workbook as it's laid out in the second image.
If we assume the Destination workbook is blank to begin with, some of the information is fixed and won't need copying across from the Source workbook but will need to be factored in by the VBA code. For example most of the column headings apart from the League Name, which is variable and the Home & Away text in column B will always be the same.
The League Name can be found in cell E2 of the Source file.
The date can be found in the relevant row in column F of the Source file.
The main part of the code needs to look at each fixture in columns F to H of the Source file coming up in the next 7 days then match the Home team in column G to the Home Rankings table in column P and copy that row of data from P to X into the Destination workbook(in columns C to K). Then match the Away team in column H to the Away Rankings table in column Z and copy that row of data from Z to AH into the next row down of the Destination workbook(in columns C to K).
I will be running the code on more than one source file but i won't need it to loop through each one. I will run the code on the first source file then close it and open the second source file then run the code again. So it will need to look for the first blank row in the Destination file each time it runs. The structure of each source file is exactly the same so as long as it works for the first it will run for them all.
I have added links below to sample files on Dropbox for you to work with.
Source File - Dropbox - sample_data.xlsx
Destination File - Dropbox - Destination.xlsx
I'm really hoping some kind soul can help me please. I've been trying to do this myself for the last couple of weeks but being a novice have had to admit defeat. I hope i've explained things well enough but please ask if you need anything clarifying.
Source Workbook
Excel 2013/2016
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Destination Workbook
Excel 2013/2016
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
If we assume the Destination workbook is blank to begin with, some of the information is fixed and won't need copying across from the Source workbook but will need to be factored in by the VBA code. For example most of the column headings apart from the League Name, which is variable and the Home & Away text in column B will always be the same.
The League Name can be found in cell E2 of the Source file.
The date can be found in the relevant row in column F of the Source file.
The main part of the code needs to look at each fixture in columns F to H of the Source file coming up in the next 7 days then match the Home team in column G to the Home Rankings table in column P and copy that row of data from P to X into the Destination workbook(in columns C to K). Then match the Away team in column H to the Away Rankings table in column Z and copy that row of data from Z to AH into the next row down of the Destination workbook(in columns C to K).
I will be running the code on more than one source file but i won't need it to loop through each one. I will run the code on the first source file then close it and open the second source file then run the code again. So it will need to look for the first blank row in the Destination file each time it runs. The structure of each source file is exactly the same so as long as it works for the first it will run for them all.
I have added links below to sample files on Dropbox for you to work with.
Source File - Dropbox - sample_data.xlsx
Destination File - Dropbox - Destination.xlsx
I'm really hoping some kind soul can help me please. I've been trying to do this myself for the last couple of weeks but being a novice have had to admit defeat. I hope i've explained things well enough but please ask if you need anything clarifying.
Source Workbook
Excel 2013/2016
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | ||||||||||||||||||||||||||||||||||
2 | League Name | HOME RANKINGS | AWAY RANKINGS | |||||||||||||||||||||||||||||||
3 | ||||||||||||||||||||||||||||||||||
4 | ||||||||||||||||||||||||||||||||||
5 | ||||||||||||||||||||||||||||||||||
6 | Rd | Date | Home | Away | Team | F | H | FHH | SHH | ATH | DFH | OFH | RFH | Team | F | A | FHA | SHA | ATA | DFA | OFA | RFA | ||||||||||||
7 | 1 | 21/08/17 | Team 1 | Team 30 | Team 1 | 2 | 3 | 9 | 3 | 4 | 18 | 3 | 3 | Team 1 | 2 | 10 | 4 | 21 | 9 | 17 | 10 | 10 | ||||||||||||
8 | 1 | 21/08/17 | Team 2 | Team 29 | Team 2 | 3 | 4 | 4 | 7 | 2 | 13 | 2 | 3 | Team 2 | 3 | 11 | 26 | 12 | 9 | 21 | 3 | 4 | ||||||||||||
9 | 1 | 21/08/17 | Team 3 | Team 28 | Team 3 | 8 | 5 | 2 | 10 | 8 | 1 | 8 | 8 | Team 3 | 8 | 17 | 10 | 24 | 17 | 5 | 8 | 5 | ||||||||||||
10 | 1 | 21/08/17 | Team 4 | Team 27 | Team 4 | 30 | 29 | 7 | 30 | 27 | 25 | 27 | 30 | Team 4 | 30 | 27 | 22 | 22 | 22 | 9 | 30 | 29 | ||||||||||||
11 | 1 | 21/08/17 | Team 5 | Team 26 | Team 5 | 13 | 27 | 27 | 22 | 24 | 22 | 24 | 13 | Team 5 | 13 | 1 | 5 | 3 | 3 | 1 | 13 | 27 | ||||||||||||
12 | 1 | 21/08/17 | Team 6 | Team 25 | Team 6 | 7 | 8 | 6 | 14 | 5 | 17 | 5 | 7 | Team 6 | 7 | 14 | 14 | 17 | 15 | 21 | 7 | 8 | ||||||||||||
13 | 1 | 21/08/17 | Team 7 | Team 24 | Team 7 | 18 | 11 | 17 | 8 | 10 | 19 | 10 | 18 | Team 7 | 18 | 26 | 23 | 23 | 27 | 9 | 18 | 11 | ||||||||||||
14 | 1 | 21/08/17 | Team 8 | Team 23 | Team 8 | 10 | 2 | 3 | 2 | 3 | 13 | 3 | 10 | Team 8 | 10 | 24 | 7 | 30 | 24 | 5 | 10 | 2 | ||||||||||||
15 | 1 | 21/08/17 | Team 9 | Team 22 | Team 9 | 6 | 10 | 15 | 5 | 5 | 25 | 5 | 6 | Team 9 | 6 | 8 | 12 | 9 | 7 | 13 | 6 | 10 | ||||||||||||
16 | 1 | 21/08/17 | Team 10 | Team 21 | Team 10 | 28 | 18 | 10 | 23 | 15 | 27 | 15 | 28 | Team 10 | 28 | 30 | 30 | 28 | 29 | 27 | 28 | 18 | ||||||||||||
17 | 1 | 21/08/17 | Team 11 | Team 20 | Team 11 | 12 | 7 | 23 | 1 | 13 | 3 | 13 | 12 | Team 11 | 12 | 21 | 18 | 16 | 22 | 27 | 12 | 7 | ||||||||||||
18 | 1 | 21/08/17 | Team 12 | Team 19 | Team 12 | 4 | 1 | 1 | 6 | 1 | 13 | 1 | 4 | Team 12 | 4 | 25 | 17 | 26 | 24 | 5 | 4 | 1 | ||||||||||||
19 | 1 | 21/08/17 | Team 13 | Team 18 | Team 13 | 25 | 25 | 19 | 27 | 24 | 30 | 24 | 25 | Team 13 | 25 | 19 | 29 | 6 | 17 | 21 | 25 | 25 | ||||||||||||
20 | 1 | 21/08/17 | Team 14 | Team 17 | Team 14 | 16 | 13 | 16 | 11 | 8 | 19 | 8 | 16 | Team 14 | 16 | 15 | 6 | 29 | 9 | 9 | 16 | 13 | ||||||||||||
21 | 1 | 21/08/17 | Team 15 | Team 16 | Team 15 | 22 | 17 | 13 | 19 | 22 | 1 | 22 | 22 | Team 15 | 22 | 18 | 25 | 15 | 17 | 17 | 22 | 17 | ||||||||||||
22 | Team 16 | 27 | 28 | 18 | 29 | 24 | 27 | 24 | 27 | Team 16 | 27 | 23 | 19 | 18 | 27 | 3 | 27 | 28 | ||||||||||||||||
23 | 2 | 04/09/17 | Team 30 | Team 16 | Team 17 | 5 | 9 | 8 | 13 | 10 | 8 | 10 | 5 | Team 17 | 5 | 7 | 8 | 20 | 5 | 17 | 5 | 9 | ||||||||||||
24 | 2 | 04/09/17 | Team 17 | Team 15 | Team 18 | 9 | 22 | 29 | 12 | 22 | 8 | 22 | 9 | Team 18 | 9 | 3 | 3 | 1 | 1 | 2 | 9 | 22 | ||||||||||||
25 | 2 | 04/09/17 | Team 18 | Team 14 | Team 19 | 14 | 12 | 14 | 9 | 10 | 8 | 10 | 14 | Team 19 | 14 | 13 | 20 | 8 | 17 | 13 | 14 | 12 | ||||||||||||
26 | 2 | 04/09/17 | Team 19 | Team 13 | Team 20 | 11 | 16 | 20 | 16 | 17 | 13 | 17 | 11 | Team 20 | 11 | 5 | 9 | 5 | 7 | 9 | 11 | 16 | ||||||||||||
27 | 2 | 04/09/17 | Team 20 | Team 12 | Team 21 | 19 | 26 | 22 | 25 | 27 | 5 | 27 | 19 | Team 21 | 19 | 4 | 1 | 4 | 4 | 8 | 19 | 26 | ||||||||||||
28 | 2 | 04/09/17 | Team 21 | Team 11 | Team 22 | 23 | 24 | 12 | 26 | 19 | 22 | 19 | 23 | Team 22 | 23 | 12 | 15 | 13 | 15 | 3 | 23 | 24 | ||||||||||||
29 | 2 | 04/09/17 | Team 22 | Team 10 | Team 23 | 1 | 14 | 5 | 24 | 13 | 8 | 13 | 1 | Team 23 | 1 | 2 | 2 | 10 | 1 | 21 | 1 | 14 | ||||||||||||
30 | 2 | 04/09/17 | Team 23 | Team 9 | Team 24 | 24 | 21 | 25 | 20 | 27 | 4 | 27 | 24 | Team 24 | 24 | 16 | 13 | 14 | 9 | 27 | 24 | 21 | ||||||||||||
31 | 2 | 04/09/17 | Team 24 | Team 8 | Team 25 | 21 | 15 | 21 | 15 | 17 | 8 | 17 | 21 | Team 25 | 21 | 22 | 16 | 11 | 9 | 30 | 21 | 15 | ||||||||||||
32 | 2 | 04/09/17 | Team 25 | Team 7 | Team 26 | 17 | 6 | 11 | 4 | 7 | 5 | 7 | 17 | Team 26 | 17 | 28 | 24 | 25 | 24 | 21 | 17 | 6 | ||||||||||||
33 | 2 | 04/09/17 | Team 26 | Team 6 | Team 27 | 15 | 20 | 26 | 18 | 19 | 5 | 19 | 15 | Team 27 | 15 | 6 | 21 | 2 | 5 | 17 | 15 | 20 | ||||||||||||
34 | 2 | 04/09/17 | Team 27 | Team 5 | Team 28 | 20 | 23 | 28 | 21 | 19 | 22 | 19 | 20 | Team 28 | 20 | 9 | 11 | 19 | 9 | 21 | 20 | 23 | ||||||||||||
35 | 2 | 04/09/17 | Team 28 | Team 4 | Team 29 | 26 | 19 | 30 | 17 | 15 | 27 | 15 | 26 | Team 29 | 26 | 29 | 28 | 27 | 29 | 13 | 26 | 19 | ||||||||||||
36 | 2 | 04/09/17 | Team 29 | Team 3 | Team 30 | 29 | 30 | 24 | 28 | 30 | 19 | 30 | 29 | Team 30 | 29 | 20 | 27 | 7 | 17 | 13 | 29 | 30 | ||||||||||||
37 | 2 | 04/09/17 | Team 1 | Team 2 | ||||||||||||||||||||||||||||||
38 | ||||||||||||||||||||||||||||||||||
39 | 3 | 05/09/17 | Team 2 | Team 30 | ||||||||||||||||||||||||||||||
40 | 3 | 05/09/17 | Team 3 | Team 1 | ||||||||||||||||||||||||||||||
41 | 3 | 05/09/17 | Team 4 | Team 29 | ||||||||||||||||||||||||||||||
42 | 3 | 05/09/17 | Team 5 | Team 28 | ||||||||||||||||||||||||||||||
43 | 3 | 05/09/17 | Team 6 | Team 27 | ||||||||||||||||||||||||||||||
44 | 3 | 05/09/17 | Team 7 | Team 26 | ||||||||||||||||||||||||||||||
45 | 3 | 05/09/17 | Team 8 | Team 25 | ||||||||||||||||||||||||||||||
46 | 3 | 05/09/17 | Team 9 | Team 24 | ||||||||||||||||||||||||||||||
47 | 3 | 05/09/17 | Team 10 | Team 23 | ||||||||||||||||||||||||||||||
48 | 3 | 05/09/17 | Team 11 | Team 22 | ||||||||||||||||||||||||||||||
49 | 3 | 05/09/17 | Team 12 | Team 21 | ||||||||||||||||||||||||||||||
50 | 3 | 05/09/17 | Team 13 | Team 20 |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1
Destination Workbook
Excel 2013/2016
A | B | C | D | E | F | G | H | I | J | K | |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | Date | League Name | F | H | FH | SH | At | DF | OF | RF | |
2 | 04/09/17 | Home | Team 30 | 13 | 15 | 4 | 16 | 11 | 15 | 16 | 15 |
3 | 04/09/17 | Away | Team 16 | 7 | 14 | 13 | 13 | 11 | 13 | 2 | 2 |
4 | 04/09/17 | Home | Team 17 | 7 | 1 | 1 | 3 | 1 | 8 | 7 | 5 |
5 | 04/09/17 | Away | Team 15 | 11 | 7 | 7 | 4 | 10 | 13 | 5 | 3 |
6 | 04/09/17 | Home | Team 18 | 9 | 13 | 3 | 8 | 1 | 7 | 11 | 5 |
7 | 04/09/17 | Away | Team 14 | 12 | 9 | 8 | 3 | 1 | 15 | 6 | 7 |
8 | 04/09/17 | Home | Team 19 | 4 | 6 | 5 | 8 | 6 | 4 | 4 | 5 |
9 | 04/09/17 | Away | Team 13 | 9 | 18 | 18 | 12 | 12 | 17 | 7 | 7 |
10 | 04/09/17 | Home | Team 20 | 4 | 8 | 11 | 5 | 5 | 14 | 3 | 9 |
11 | 04/09/17 | Away | Team 12 | 10 | 8 | 1 | 13 | 4 | 10 | 15 | 6 |
12 | 04/09/17 | Home | Team 21 | 7 | 1 | 1 | 3 | 1 | 8 | 7 | 5 |
13 | 04/09/17 | Away | Team 11 | 11 | 7 | 7 | 4 | 10 | 13 | 5 | 3 |
14 | 04/09/17 | Home | Team 22 | 9 | 13 | 3 | 8 | 1 | 7 | 11 | 5 |
15 | 04/09/17 | Away | Team 10 | 12 | 9 | 8 | 3 | 1 | 15 | 6 | 7 |
16 | 04/09/17 | Home | Team 23 | 4 | 6 | 5 | 8 | 6 | 4 | 4 | 5 |
17 | 04/09/17 | Away | Team 9 | 9 | 18 | 18 | 12 | 12 | 17 | 7 | 7 |
18 | 04/09/17 | Home | Team 24 | 4 | 8 | 11 | 5 | 5 | 14 | 3 | 9 |
19 | 04/09/17 | Away | Team 8 | 10 | 8 | 1 | 13 | 4 | 10 | 15 | 6 |
20 | 04/09/17 | Home | Team 25 | 7 | 1 | 1 | 3 | 1 | 8 | 7 | 5 |
21 | 04/09/17 | Away | Team 7 | 11 | 7 | 7 | 4 | 10 | 13 | 5 | 3 |
22 | 04/09/17 | Home | Team 26 | 9 | 13 | 3 | 8 | 1 | 7 | 11 | 5 |
23 | 04/09/17 | Away | Team 6 | 12 | 9 | 8 | 3 | 1 | 15 | 6 | 7 |
24 | 04/09/17 | Home | Team 27 | 4 | 6 | 5 | 8 | 6 | 4 | 4 | 5 |
25 | 04/09/17 | Away | Team 5 | 9 | 18 | 18 | 12 | 12 | 17 | 7 | 7 |
26 | 04/09/17 | Home | Team 28 | 4 | 8 | 11 | 5 | 5 | 14 | 3 | 9 |
27 | 04/09/17 | Away | Team 4 | 10 | 8 | 1 | 13 | 4 | 10 | 15 | 6 |
28 | 04/09/17 | Home | Team 29 | 10 | 8 | 1 | 13 | 4 | 10 | 15 | 6 |
29 | 04/09/17 | Away | Team 3 | 10 | 8 | 1 | 13 | 4 | 10 | 15 | 6 |
30 | 04/09/17 | Home | Team 1 | 10 | 8 | 1 | 13 | 4 | 10 | 15 | 6 |
31 | 04/09/17 | Away | Team 2 | 10 | 8 | 1 | 13 | 4 | 10 | 15 | 6 |
32 | Date | League 2 Name | F | H | FH | SH | At | DF | OF | RF | |
33 | 04/09/17 | Home | Team 4 | 7 | 1 | 1 | 3 | 1 | 8 | 7 | 5 |
34 | 04/09/17 | Away | Team 2 | 11 | 7 | 7 | 4 | 10 | 13 | 5 | 3 |
35 | 04/09/17 | Home | Team 5 | 9 | 13 | 3 | 8 | 1 | 7 | 11 | 5 |
36 | 04/09/17 | Away | Team 6 | 12 | 9 | 8 | 3 | 1 | 15 | 6 | 7 |
37 | 04/09/17 | Home | Team 7 | 4 | 6 | 5 | 8 | 6 | 4 | 4 | 5 |
38 | 04/09/17 | Away | Team 8 | 9 | 18 | 18 | 12 | 12 | 17 | 7 | 7 |
39 | 04/09/17 | Home | Team 9 | 4 | 8 | 11 | 5 | 5 | 14 | 3 | 9 |
40 | 04/09/17 | Away | Team 10 | 4 | 6 | 5 | 8 | 6 | 4 | 4 | 5 |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1