Dynamic Macro to copy and paste

[ferr]

I have 2 sheets, call it ‘upload’ and ‘final’.
There are calculations that are run behind the scenes I.e. other tabs that require Columns E:AN from the upload tab to be copied and pasted one at a time to the Final tab.
Need a macro, that essentially does this.

1. Copies E56,E61,E66 from Upload tab and pastes in final tab by looking up the participants in column A and matching to the participants in the upload tab (B56,B61,B66) and the dates in row 1 and matching to the date in row 48 in the upload tab.

For example, E56,E61 and E66 will be pasted in F2,F3 and F4 in the final tab.

1. The macro will then continue doing this so, it will copy the next 3 in the upload tab E57,E62,E67 and paste in the appropriate cells in the final tab until we get to the last column say AN.

The cells in the upload tab need to be filled up one at a time (3 participants at a time) for the calculations to work, hence why an INDEX MATCH won’t work as the numbers change once pasted.
Also needs to be dynamic, hence why it looks ups the participants and date before pasting as the dates or participants will change.
Thank you.

test macro.xlsx
	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	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB
46	Participants
47
48			Row Labels	11/30/2024	12/31/2024	01/31/2025	02/28/2025	03/31/2025	04/30/2025	05/31/2025	06/30/2025	07/31/2025	08/31/2025	09/30/2025	10/31/2025	11/30/2025	12/31/2025	01/31/2026	02/28/2026	03/31/2026	04/30/2026	05/31/2026	06/30/2026	07/31/2026	08/31/2026	09/30/2026	10/31/2026	11/30/2026	12/31/2026	01/31/2027	02/28/2027	03/31/2027	04/30/2027	05/31/2027	06/30/2027	07/31/2027	08/31/2027	09/30/2027	10/31/2027	11/30/2027	12/31/2027	01/31/2028	02/29/2028	03/31/2028	04/30/2028	05/31/2028	06/30/2028	07/31/2028	08/31/2028	09/30/2028	10/31/2028	11/30/2028	12/31/2028
49	A			9,859	9,859	9,859	9,859	9,859	9,859	9,859	9,859	9,859	9,859	9,859	9,859	9,859	9,859	9,859	9,859	9,859	9,859	9,859	9,859	9,859	9,859	9,859	9,859	9,859	9,859	9,859	9,859	9,859	9,859	9,859	9,859	9,859	9,859	9,859	9,859	9,859	9,859	9,859	9,859	9,859
50	B			5,444	5,444	5,444	5,444	5,444	5,444	5,444	5,444	5,444	5,444	5,444	5,444	5,444	5,444	5,444	5,444	5,444	5,444	5,444	5,444	5,444	5,444	5,444	5,444	5,444	5,444	5,444	5,444	5,444	5,444	5,444	5,444	5,444	5,444	5,444	5,444	5,444	5,444	5,444	5,444	5,444
51	C			6,555	6,555	6,555	6,555	6,555	6,555	6,555	6,555	6,555	6,555	6,555	6,555	6,555	6,555	6,555	6,555	6,555	6,555	6,555	6,555	6,555	6,555	6,555	6,555	6,555	6,555	6,555	6,555	6,555	6,555	6,555	6,555	6,555	6,555	6,555	6,555	6,555	6,555	6,555	6,555	6,555
52				585,858,585	585,858,585	585,858,585	585,858,585	585,858,585	585,858,585	585,858,585	585,858,585	585,858,585	585,858,585	585,858,585	585,858,585	585,858,585	585,858,585	585,858,585	585,858,585	585,858,585	585,858,585	585,858,585	585,858,585	585,858,585	585,858,585	585,858,585	585,858,585	585,858,585	585,858,585	585,858,585	585,858,585	585,858,585	585,858,585	585,858,585	585,858,585	585,858,585	585,858,585	585,858,585	585,858,585	585,858,585	############	############	585,858,585	585,858,585
53
54	A
55	A
56	A			395,949,495	4,696,969	6,464	46,464	46,464	46,464	46,464	46,464	46,464	46,464	46,464	46,464	46,464	46,464	46,464	46,464	46,464	46,464	46,464	46,464	46,464	46,464	46,464	46,464	46,464	46,464	46,464	46,464	46,464	46,464	46,464	454	335	453	6,565	46,464	46,464	46,464	46,464	46,464	5,555	433	-
57
58
59	B
60	B
61	B			4,546,567	6,766	46,464	817,683	817,683	817,683	817,683	817,683	817,683	817,683	817,683	817,683	817,683	817,683	817,683	817,683	817,683	817,683	817,683	817,683	817,683	817,683	817,683	817,683	817,683	817,683	817,683	817,683	817,683	3,422	817,683	3,433	3,423	817,683	817,683	817,683	3,443	343	817,683	34,343,434	343	-	-
62
63
64	C
65	C
66	C			7,676	656,565	8,877	78	78	78	78	78	78	78	78	78	78	78	78	78	78	78	78	3,434,343	4,343,433	78	78	78	78	78	78	78	78	78	3,433	34,343	78	3,434	78	78	78	78	78	78	343	43,433	-
Upload

Cell Formulas
Range	Formula
B54:B55,B64:B65,B59:B60	B54	=B55
B56	B56	=B49
B61	B61	=B50
B66	B66	=B51

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E65:AU65	Expression	=E$45=$E$45+2	text	NO
E65:AU65	Expression	=E$45=$E$45+1	text	NO
E65:AU65	Expression	=E$45=2025	text	NO
E55:AU55	Expression	=E$45=$E$45+2	text	NO
E55:AU55	Expression	=E$45=$E$45+1	text	NO
E55:AU55	Expression	=E$45=2025	text	NO
E46:N46,S46:AB46,AG46:AP46,AU46:BB46	Cell Value	=FALSE	text	NO
E46:N46,S46:AB46,AG46:AP46,AU46:BB46	Cell Value	=TRUE	text	NO

test macro.xlsx
	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	AI	AJ	AK	AL	AM	AN	AO
1	Participants	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24	Jan-25	Feb-25	Mar-25	Apr-25	May-25	Jun-25	Jul-25	Aug-25	Sep-25	Oct-25	Nov-25	Dec-25	Jan-26	Feb-26	Mar-26	Apr-26	May-26	Jun-26	Jul-26	Aug-26	Sep-26	Oct-26	Nov-26	Dec-26	Jan-27	Feb-27	Mar-27	Apr-27	May-27	Jun-27	Jul-27	Aug-27	Sep-27	Oct-27
2	A	8,787,878.00	54,565,465.00	67,676.00	6,676.00
3	B	6,767,678.00	56,655.00	7,645,435.00	754.00
4	C	34,434,343.00	767,667.00	54,544.00	9,548.00
5
6	D	6,767.00	4,545.00	565,656,564.00	76,764.00
7	E	4,545.00	6,767.00	7,676.00	4,354,544.00
8	F	-	-	-	-
9
10	G	8,599.00	545,454.00	454.00	4,545.00
11	H	545.00	-	94,394.00	454.00
12	II	454.00	76.00	545,466,565.00	-
13
14	J	4,532.00	4,545.00	4,545.00	567,786.00
15	K	-	-	-	-
16	L	2,324,324.00	54,545.00	65,656,565.00	67,657.00
17
18	M	-	-	-	-
19	N	7,676.00	5,656.00	665,656.00	56,565.00
20	O	-	-	-	56,565.00
21		-	-		-
22	P	-	-	-	-
23	Q	765,434.00	4,565,656.00	56,567.00	6,565.00
24	R	-	-	-	-
25		-	-	-	-
26	S	5,555.00	22,466,777.00	343,456.00	75,665,565.00
27	T	55,567,676.00	4,554.00	67,645,453.00	56,565,643.00
28	U	-	-	-	-
29		-	-	-	-
30	V	5,454.00	5,653.00	-	54,453.00
31	W	554,567.00	898,989.00	78,708.00	434,345.00
32	X	424.00	3.00	76,545.00	56,677,667.00
33		-	-	-	-
34	Y	43,243,435.00	545.00	5,454.00	22,323.00
35	Z	-	-	-	-
36	AB1	5,454.00	343.00	-	232.00
37		-	-	-	-
38	QC2	-	354,545.00	543,342.00	-
39	DF4
40	DG6	5.00	545,454.00	54.00	544.00
Final

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O2:AM38	Cell Value	<0	text	NO

 

[ferr]

Apologies, rephrased but posted in another thread.

Macro Lookup, copy and paste

1. Is there a more efficient code than the code below, as I have to run it for each participant group i.e. 10 times and VBA won't allow it because it is too long? 2. Instead of the code starting to paste in column H as the dates will change frequently, can it look up the date in the upload tab...

www.mrexcel.com

 

[Fluff]

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

On this occasion I have merged both threads.