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]

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 row 48 and match it to the date in the final tab row 1 before pasting?

Sheets("UPLOAD").Select

    Range("G50").Select

    ActiveSheet.PivotTables("PivotTable7").PivotCache.Refresh

    Range("E56,E61,E66").Select

    Range("E66").Activate

    Selection.Copy

    Sheets("Final").Select

    Range("H2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Sheets("UPLOAD").Select

    Range("F56,F61,F66").Select

    Range("F66").Activate

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Final").Select

    Range("I2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Sheets("UPLOAD").Select

    Range("G56,G61,G66").Select

    Range("G66").Activate

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Final").Select

    Range("J2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Sheets("UPLOAD").Select

    Range("H56,H61,H66").Select

    Range("H66").Activate

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Final").Select

    Range("K2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Sheets("UPLOAD").Select

    Range("I56,I61,I66").Select

    Range("I66").Activate

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Final").Select

    Range("L2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Sheets("UPLOAD").Select

    Range("J56,J61,J66").Select

    Range("J66").Activate

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Final").Select

    Range("M2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Sheets("UPLOAD").Select

    Range("K56,K61,K66").Select

    Range("K66").Activate

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Final").Select

    Range("N2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Sheets("UPLOAD").Select

    Range("L56,L61,L66").Select

    Range("L66").Activate

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Final").Select

    Range("O2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Sheets("UPLOAD").Select

    Range("M56,M61,M66").Select

    Range("M66").Activate

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Final").Select

    Range("P2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

     Sheets("UPLOAD").Select

    Range("N56,N61,N66").Select

    Range("N66").Activate

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Final").Select

    Range("Q2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Sheets("UPLOAD").Select

    Range("O56,O61,O66").Select

    Range("O66").Activate

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Final").Select

    Range("R2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Sheets("UPLOAD").Select

    Range("P56,P61,P66").Select

    Range("P66").Activate

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Final").Select

    Range("S2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Sheets("UPLOAD").Select

    Range("Q56,Q61,Q66").Select

    Range("Q66").Activate

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Final").Select

    Range("T2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Sheets("UPLOAD").Select

    Range("R56,R61,R66").Select

    Range("R66").Activate

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Final").Select

    Range("U2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Sheets("UPLOAD").Select

    Range("S56,S61,S66").Select

    Range("S66").Activate

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Final").Select

    Range("V2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Sheets("UPLOAD").Select

    Range("T56,T61,T66").Select

    Range("T66").Activate

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Final").Select

    Range("W2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Sheets("UPLOAD").Select

    Range("U56,U61,U66").Select

    Range("U66").Activate

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Final").Select

    Range("X2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

       

        Sheets("UPLOAD").Select

    Range("V56,V61,V66").Select

    Range("V66").Activate

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Final").Select

    Range("Y2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Sheets("UPLOAD").Select

    Range("W56,W61,W66").Select

    Range("W66").Activate

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Final").Select

    Range("Z2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Sheets("UPLOAD").Select

    Range("X56,X61,X66").Select

    Range("X66").Activate

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Final").Select

    Range("AA2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Sheets("UPLOAD").Select

    Range("Y56,Y61,Y66").Select

    Range("Y66").Activate

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Final").Select

    Range("AB2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Sheets("UPLOAD").Select

    Range("Z56,Z61,Z66").Select

    Range("Z66").Activate

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Final").Select

    Range("AC2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Sheets("UPLOAD").Select

    Range("AA56,AA61,AA66").Select

    Range("AA66").Activate

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Final").Select

    Range("AD2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Sheets("UPLOAD").Select

    Range("AB56,AB61,AB66").Select

    Range("AB66").Activate

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Final").Select

    Range("AE2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Sheets("UPLOAD").Select

    Range("AC56,AC61,AC66").Select

    Range("AC66").Activate

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Final").Select

    Range("AF2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

     Sheets("UPLOAD").Select

    Range("AD56,AD61,AD66").Select

    Range("AD66").Activate

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Final").Select

    Range("AG2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Sheets("UPLOAD").Select

    Range("AE56,AE61,AE66").Select

    Range("AE66").Activate

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Final").Select

    Range("AH2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Sheets("UPLOAD").Select

    Range("AF56,AF61,AF66").Select

    Range("AF66").Activate

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Final").Select

    Range("AI2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Sheets("UPLOAD").Select

    Range("AG56,AG61,AG66").Select

    Range("AG66").Activate

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Final").Select

    Range("AJ2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Sheets("UPLOAD").Select

    Range("AH56,AH61,AH66").Select

    Range("AH66").Activate

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Final").Select

    Range("AK2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Sheets("UPLOAD").Select

    Range("AI56,AI61,AI66").Select

    Range("AI66").Activate

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Final").Select

    Range("AL2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Sheets("UPLOAD").Select

    Range("AJ56,AJ61,AJ66").Select

    Range("AJ66").Activate

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Final").Select

    Range("AM2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Sheets("UPLOAD").Select

    Range("AK56,AK61,AK66").Select

    Range("AK66").Activate

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Final").Select

    Range("AN2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

 

[ferr]

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 row 48 and match it to the date in the final tab row 1 before pasting?

Sheets("UPLOAD").Select

Range("G50").Select

ActiveSheet.PivotTables("PivotTable7").PivotCache.Refresh

Range("E56,E61,E66").Select

Range("E66").Activate

Selection.Copy

Sheets("Final").Select

Range("H2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("F56,F61,F66").Select

Range("F66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("I2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("G56,G61,G66").Select

Range("G66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("J2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("H56,H61,H66").Select

Range("H66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("K2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("I56,I61,I66").Select

Range("I66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("L2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("J56,J61,J66").Select

Range("J66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("M2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("K56,K61,K66").Select

Range("K66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("N2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("L56,L61,L66").Select

Range("L66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("O2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("M56,M61,M66").Select

Range("M66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("P2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("N56,N61,N66").Select

Range("N66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("Q2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("O56,O61,O66").Select

Range("O66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("R2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("P56,P61,P66").Select

Range("P66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("S2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("Q56,Q61,Q66").Select

Range("Q66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("T2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("R56,R61,R66").Select

Range("R66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("U2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("S56,S61,S66").Select

Range("S66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("V2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("T56,T61,T66").Select

Range("T66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("W2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("U56,U61,U66").Select

Range("U66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("X2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False



Sheets("UPLOAD").Select

Range("V56,V61,V66").Select

Range("V66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("Y2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("W56,W61,W66").Select

Range("W66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("Z2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("X56,X61,X66").Select

Range("X66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("AA2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("Y56,Y61,Y66").Select

Range("Y66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("AB2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("Z56,Z61,Z66").Select

Range("Z66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("AC2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("AA56,AA61,AA66").Select

Range("AA66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("AD2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("AB56,AB61,AB66").Select

Range("AB66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("AE2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("AC56,AC61,AC66").Select

Range("AC66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("AF2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("AD56,AD61,AD66").Select

Range("AD66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("AG2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("AE56,AE61,AE66").Select

Range("AE66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("AH2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("AF56,AF61,AF66").Select

Range("AF66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("AI2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("AG56,AG61,AG66").Select

Range("AG66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("AJ2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("AH56,AH61,AH66").Select

Range("AH66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("AK2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("AI56,AI61,AI66").Select

Range("AI66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("AL2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("AJ56,AJ61,AJ66").Select

Range("AJ66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("AM2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("UPLOAD").Select

Range("AK56,AK61,AK66").Select

Range("AK66").Activate

Application.CutCopyMode = False

Selection.Copy

Sheets("Final").Select

Range("AN2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

 

[StephenCrump]

Sub TryThis()

    Dim rngCopy As Range
    Dim i As Long, inc As Long, NoSteps As Long
    
    Set rngCopy = Worksheets("Upload").Range("E56:AK56")
    NoSteps = 3     'It sounds like you need 10?
    inc = 5
    
    With Worksheets("Final").Range("H2:AN2")
        For i = 0 To NoSteps - 1
            .Offset(i).Value = rngCopy.Offset(i * inc).Value
        Next i
    End With
    
End Sub

 

[StephenCrump]

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 row 48 and match it to the date in the final tab row 1 before pasting?

Can you please explain in more detail what you mean by this? (Bearing in mind that we have no idea where your dates are, and which dates you want to match).

 

[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 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.

2. 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 up date before pasting as the dates 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
43
44
45
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
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
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	-
67
68
69
70
Upload

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

 

[StephenCrump]

I don't know if you saw Post #2? That code does everything that the code in Post #1 does.

Your Upload screenshot shows results for:

A in row 56
B in row 61
C in row 66

Does this pattern continue, e.g.

D in row 71
E in row 76
F in row 81?

If so, we can wrap the code in Post #2 in a loop that iterates through the ten groups.

Alternatively, we could use a formula (or have VBA use that formula, and replace with values). Assuming the above pattern continues, and assuming you want all the columns copied across, this formula in Final!F2 should populate the required results:

=LET(Upload,IF(Upload!E56:AV318="","",Upload!E56:AV318),inc,5,grp,ROWS(A2:A5),s,SEQUENCE(ROWS(A2:A41),,0),r,1+inc*s-inc*INT(s/grp),x,IF(MOD(s,grp)=grp-1,"",CHOOSEROWS(Upload,r)),x)

However ...

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.

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.

Sorry, I don't follow this at all. What does "behind the scenes" mean? Were you correct in saying "the cells in the upload tab need to be filled up one at a time"?

I am only guessing that:

- You do want all columns, not just the November 2024 column, to be pasted into Final
- The December 2024 and subsequent columns will be overwritten when the next Upload is available?

 

[ferr]

I don't know if you saw Post #2? That code does everything that the code in Post #1 does.

Your Upload screenshot shows results for:

A in row 56
B in row 61
C in row 66

Does this pattern continue, e.g.

D in row 71
E in row 76
F in row 81?

If so, we can wrap the code in Post #2 in a loop that iterates through the ten groups.

Alternatively, we could use a formula (or have VBA use that formula, and replace with values). Assuming the above pattern continues, and assuming you want all the columns copied across, this formula in Final!F2 should populate the required results:

=LET(Upload,IF(Upload!E56:AV318="","",Upload!E56:AV318),inc,5,grp,ROWS(A2:A5),s,SEQUENCE(ROWS(A2:A41),,0),r,1+inc*s-inc*INT(s/grp),x,IF(MOD(s,grp)=grp-1,"",CHOOSEROWS(Upload,r)),x)

However ...


Sorry, I don't follow this at all. What does "behind the scenes" mean? Were you correct in saying "the cells in the upload tab need to be filled up one at a time"?

I am only guessing that:

- You do want all columns, not just the November 2024 column, to be pasted into Final
- The December 2024 and subsequent columns will be overwritten when the next Upload is available?

thank you. sorry, I should have been clearer. behind the scenes- in the upload sheet, the cells to be copied are formula driven, so once the first column is pasted in the final sheet, the values for the next column changes in the upload sheet. its an amortization schedule, so the values change once the values are copied and pasted.
hence, they need to be copied and pasted one after the other.
ignore everything apart from below, the idea is that once I have the code, I can replicate it in the other iterations for the other groups by just changing the column and row numbers.

I need the macro to

1. copy the values from the upload sheet in E56,E61,E66
2. FIND THE STARTING POINT to paste the data- lookup the date in upload sheet E48 and match the date in the final sheet row 1. (right now, it will be F1 i.e November 2024, but in another period, November 2024 might be in E1 or D1.
3. once the appropriate column is matched in this case F1, paste the values in rows F2,F3,F4.
4. then since we have the starting point for the paste as F2,F3,F4- copy the next set of values from the upload sheet which will be F56,F61,F66 then paste in the next column in the final sheet so, G2,G3,G4.
5. then copy from upload sheet G56,G61,G66 and paste in the final sheet H2,H3,H4.
6. keep doing this one column to the right at a time until column AN in the upload sheet is copied and pasted.

its just a copy and paste code one at a time, but it needs to find the starting point for where it should paste by looking up the date.

 

[ferr]

Just following up, thank you for your help.

 

[ferr]

I don't know if you saw Post #2? That code does everything that the code in Post #1 does.

Your Upload screenshot shows results for:

A in row 56
B in row 61
C in row 66

Does this pattern continue, e.g.

D in row 71
E in row 76
F in row 81?

If so, we can wrap the code in Post #2 in a loop that iterates through the ten groups.

Alternatively, we could use a formula (or have VBA use that formula, and replace with values). Assuming the above pattern continues, and assuming you want all the columns copied across, this formula in Final!F2 should populate the required results:

=LET(Upload,IF(Upload!E56:AV318="","",Upload!E56:AV318),inc,5,grp,ROWS(A2:A5),s,SEQUENCE(ROWS(A2:A41),,0),r,1+inc*s-inc*INT(s/grp),x,IF(MOD(s,grp)=grp-1,"",CHOOSEROWS(Upload,r)),x)

However ...


Sorry, I don't follow this at all. What does "behind the scenes" mean? Were you correct in saying "the cells in the upload tab need to be filled up one at a time"?

I am only guessing that:

- You do want all columns, not just the November 2024 column, to be pasted into Final
- The December 2024 and subsequent columns will be overwritten when the next Upload is available?

Not sure if you saw the reply. Please are you able to help. Thank you.