Programmatically transfer data from one excel file to another excel file

[nikadon]

Good day,

I have a few excel files one is a compilation of all the data submitted on the individual activity sheet that is completed daily. I want to programmatically extract the data from all the activity sheets to the dashboard monthly. Please see attached two files with examples of what is stored. Any help will be appreciated as there are numerous locations with many activity sheets to capture the data from.
Any help is welcomed.
Regards,

EurekaMSR_TellService Dashboard2021.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	DEPOSITS
2	WEEK	0
3	MSR/TELLER	DAILY					WEEKLY			YTD
4		4-Jan-21	5-Jan-21	6-Jan-21	7-Jan-21	8-Jan-21	8-Jan-21			8-Jan-21
5		Mon	Tue	Wed	Thu	Fri	Actual	Target	Difference	Actual	Target	Difference
6	Ava Gaye	$ -	$ -	$ -	$ -	$ -	-	-	-	-	-	-
7	Camille	$ -	$ -	$ -	$ 64,000.00	$ 40,000.00	104,000	-	- 104,000	104,000	-	- 104,000
8	Carlton						-	-	-	-	-	-
9	Cassandra						-	-	-	-	-	-
10	Davidia						-	-	-	-	-	-
11	0						-	-	-	-	-	-
12	0						-	-	-	-	-	-
13	0						-	-	-	-	-	-
14	0						-	-	-	-	-	-
15	0						-	-	-	-	-	-
16	0						-	-	-	-	-	-
17	0						-	-	-	-	-	-
18	0						-	-	-	-	-	-
19	0						-	-	-	-	-	-
20	0						-	-	-	-	-	-
21	0						-	-	-	-	-	-
22	0						-	-	-	-	-	-
23	0						-	-	-	-	-	-
24	TOTAL	0	0	0	64000	40000	104000	0	-104000	104000	0	-104000
MSR_Teller Service Dashboard

Cell Formulas
Range	Formula
B4	B4	=B179
C4:F4	C4	=B4+1
G4	G4	=F4
J4,J6:K23	J4	=G4
B6	B6	='\\fhccutil\ServiceImprovement\EurekaLawrenceTavern\2021\Ava-GayTucker\[Teller_MSRActivitySheet2021.xlsm]04.01.21'!$F$15
C6	C6	='\\fhccutil\ServiceImprovement\EurekaLawrenceTavern\2021\Ava-GayTucker\[Teller_MSRActivitySheet2021.xlsm]05.01.21'!$F$15
D6	D6	='\\fhccutil\ServiceImprovement\EurekaLawrenceTavern\2021\Ava-GayTucker\[Teller_MSRActivitySheet2021.xlsm]06.01.21'!$F$15
E6	E6	='\\fhccutil\ServiceImprovement\EurekaLawrenceTavern\2021\Ava-GayTucker\[Teller_MSRActivitySheet2021.xlsm]07.01.21'!$F$15
F6	F6	='\\fhccutil\ServiceImprovement\EurekaLawrenceTavern\2021\Ava-GayTucker\[Teller_MSRActivitySheet2021.xlsm]08.01.21'!$F$15
B7	B7	='\\fhccutil\ServiceImprovement\EurekaLawrenceTavern\2021\Camille Blair\[Teller_MSRActivitySheet2021.xlsm]04.01.21'!$F$15
C7	C7	='\\fhccutil\ServiceImprovement\EurekaLawrenceTavern\2021\Camille Blair\[Teller_MSRActivitySheet2021.xlsm]05.01.21'!$F$15
D7	D7	='\\fhccutil\ServiceImprovement\EurekaLawrenceTavern\2021\Camille Blair\[Teller_MSRActivitySheet2021.xlsm]06.01.21'!$F$15
E7	E7	='\\fhccutil\ServiceImprovement\EurekaLawrenceTavern\2021\Camille Blair\[Teller_MSRActivitySheet2021.xlsm]07.01.21'!$F$15
F7	F7	='\\fhccutil\ServiceImprovement\EurekaLawrenceTavern\2021\Camille Blair\[Teller_MSRActivitySheet2021.xlsm]08.01.21'!$F$15
G6:G23	G6	=SUM(B6:F6)
H6:H23	H6	='Target Sheet'!$K27
I6:I23,L6:L23	I6	=H6-G6
A6:A23	A6	='Target Sheet'!C27
B24:L24	B24	=SUM(B6:B23)

Teller_MSRActivitySheet2021.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1				TELLER/MEMBER SERVICE REPRESENTATIVE
2															Daily	YTD
3												Total Transactions Processed				0
4												Total Member Service Interactions (General)			8	140
5			DATE:									Total Member Service Interactions (Loans - CIAF & My Cash)			0	0
6			NAME:									Total Posting Discrepancies - Reversals			0	0
7												Total No. of Closed Referrals			7	151
8												Total New Leads			0	5
9												Coaching Session			N
10
11
12			CLOSED REFERRALS
13				Daily Count of Persons that make up $value	YTD Total Count of Persons that make up $value	Daily				Weekly				YTD		Annual
14						Actual		Target		Actual		Target		Actual		Target
15			DEPOSITS	0	7	$0.00		$25,000.00		$1,760,000.00		$125,000.00		$1,884,000.00		$6,000,000.00
16			LOANS	2	14	$425,000.00		$100,000.00		$5,787,193.72		$500,000.00		$6,952,255.07		$24,000,000.00
17			DEBIT CARD	3	39	3		1		8		5		39		120
18			MEMBERSHIP	0	27	0		1		4		5		27		96
19			ITRANSACT	0	25	0		1		3		5		25		120
20			FIP	1	13	1		1		3		5		13		120
21			LEADS	1	26	1		2		9		10		26		480
29.01.21

Cell Formulas
Range	Formula
E15:E21,P3:P8	P3	=O3+'28.01.21'!P3
O4	O4	=SUBTOTAL(3,D25:D48)
O5	O5	=SUBTOTAL(3,D53:D76)
O6	O6	=SUBTOTAL(3,D81:D95)
O7	O7	=SUBTOTAL(9,D15:D21)
O8	O8	=SUBTOTAL(3,D121:D135)
H15:H21,P15:P21	H15	='04.01.21'!H15
J15:J21,L15:L21	J15	=F15+'28.01.21'!J15
N15:N21	N15	=F15+'28.01.21'!N15

Cells with Data Validation
Cell	Allow	Criteria
D5	Date	between 1/11/2019 and NOW()
O9:O10	List	Y,N
I8	List	Y,N