VBA Find Entries Dated Within Next 7 Days And Copy Matching Rows Into Another Workbook

[annuity]

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

	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​

 

[annuity]

Just wanted to add that the example source file i've provided with 30 teams is the maximum number of teams that any other source file will have so the code doesn't need to do full column ranges. Columns F to H will only need to go up to row 933 and columns P to X and Z to AH will only need to go to row 36. All other source files will fit within those ranges. Thanks.

 

[annuity]

One final thing which might be worth mentioning is that the Source filenames will be different so it would be great if a specific filename wasn't referenced in the code for that but the Destination filename will always be the same so that can be. Thanks.

 

[annuity]

Posted this in the wee hours. Hoping someone can help me with the above if possible please.

 

[MrExcel]

Would you consider this:
1) Have the macro code sitting inside the destination workbook
2) To run the macro, you would open the destination workbook and keep it open in the background. You would then open the Source workbook. Press a hotkey to invoke the macro.
3) The macro would always pull data from the active workbook and place the records in the destination workbook below any previous records.

 

[annuity]

Hi MrExcel. Yes option 2 would be my preference please. Thanks.

 

[annuity]

Just wanted to mention that the date format in column F of the Source file has now changed from my original post and sample data. It now includes the time and is formatted as follows 04.09. 19:30