VBA Macros to Copy Specific Cells Values from Multiple Sheets to Specific Cells in Summary Sheets

[chittunoo]

Hi Mr. Expert,
Please. Can anyone help me with this VBA Macros to run for Daily Data Copying?

I have attached -
1. Daily recorded file (Filename include Month & Year) with Sheets (Named as each day of that month) (Data are recorded in Formatted Table) : from where only values from Specific Multiple Cells will be copied
2. Daily/Monthly Summary File with each Sheets summarized for Each fiscal Year: e.g. 2023 - 2024. -> Multiple Copied values to be pasted at corresponding Multiple Cells at respective Date Column from respective Year Sheet.

I had recorded Macros in Manual Copying to show how it will be copied. Due to my limited VBA skills, I was unable code them exactly as I wanted.
Can any Experts be willing to help me with?

Daily Records (Nov 2023).xlsx
	A	B	C	D	E	F	G	H
1			19.11.2023		(အလှကုန်)
2
3		လက်ငင်း	Bank A/C	အကြွေး	Discount		ငွေသုံး
4	S3231119001 - လက်လီ						မုန့်	14,300
5	S3231119002 - မမိုးမိုးစမ်း (ဓနုဖြူ)	249,100
6	S3231119003	76,500
7	S3231119004
8	S3231119005
9	S3231119006
10	S3231119007
11	S3231119008
12	S3231119009							14,300
13	S3231119010
14	S3231119011						အကြွေးရှင်း / ငွေရှင်း
15	S3231119012
16	S3231119013
17	S3231119014
18	S3231119015							0
19
20							အကြွေးဝယ်
21
22
23
24								0
25
26		325,600	0	0	0		ငွေလွှဲထုတ်ပေး / စိုက်ရှင်း
27
28	အကြွေးရ	ငွေသား	Bank A/C	Discount	မှတ်ချက်
29
30
31
32								0
33
34							Total Cash	311,300
35							အပ်ငွေ
36		0	0	0			ပိုငွေ / လိုငွေ	-311,300
19.11.2023

Cell Formulas
Range	Formula
H12	H12	=SUM(H4:H11)
H18,H24	H18	=SUM(H15:H17)
B26:E26	B26	=SUM(B4:B25)
H32	H32	=SUM(H27:H31)
H34	H34	=B26+B36-H12-H18-H32-SUMIF(B4:B25,">0",E4:E25)-SUMIF(B29:B35,">0",D29:D35)
B36:D36	B36	=SUM(B29:B35)
H36	H36	=H35-H34

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H36	Cell Value	>=0	text	NO
H36	Cell Value	<0	text	NO

2.

Yearly Records.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
111	November 2023
112	အရောင်း	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Sat
113		1-Nov	2-Nov	3-Nov	4-Nov	5-Nov	6-Nov	7-Nov	8-Nov	9-Nov	10-Nov	11-Nov	12-Nov	13-Nov	14-Nov	15-Nov	16-Nov	17-Nov	18-Nov	19-Nov	20-Nov	21-Nov	22-Nov	23-Nov	24-Nov	25-Nov	26-Nov	27-Nov	28-Nov	29-Nov	30-Nov		Total
114	Cash (လက်ငင်း)	2,221,800	1,659,650	1,868,950	376,800	1,903,900		2,014,850	747,200	265,700	167,100	1,044,950		1,721,050	2,267,300	51,700	311,300	356,550	1,520,350														18,499,150
115	Bank (ငွေလွှဲ)								1,632,000			179,700			444,400	872,000	63,000																3,191,100
116	Credits (ကြွေးပေး)	8,964,150	4,222,000	4,704,200	1,973,050			2,139,600	1,194,750	656,600	2,330,500			3,180,000	4,700,800	2,817,350			294,700														37,177,700
117	Discount (လျှော့ငွေ)	20,350		1,400				200	12,750					4,300	200	26,050																	65,250
118	Debits (ကြွေးရ - ငွေသား)		6,491,800			819,150				10,000	3,498,000				451,200	2,226,000																	13,496,150
119	Debits (ကြွေးရ - Bank A/C)	309,600	4,632,900	3,523,300					1,056,000	494,300	4,909,700			996,400	3,125,800		3,147,600	329,300															22,524,900
120	Discount (ကြွေးလျော့ငွေ)																																0
121	Expenses (သုံး - ငွေသား)	1,000	91,600	17,500	700	3,600		61,500	154,800	90,800	76,800	43,600		3,200	16,600	72,000	2,200	25,100	26,600														687,600
122	ဆိုင်ပစ္စည်းယူ (ငွေရှင်း)																																0
123	ဆိုင်ပစ္စည်းယူ (ကြွေး)																																0
124	Cash Out (ငွေလွှဲထုတ် / စိုက်ရှင်း)								205,500			275,850				64,500																	545,850
125	Total Cash Sales (ရငွေ - ငွေသား)	2,220,800	8,059,850	1,851,450	376,100	2,719,450	0	1,953,150	565,800	187,900	3,588,300	725,500	0	1,713,550	2,701,800	2,141,200	309,100	331,450	1,493,750		0	0	0	0	0	0	0	0	0	0	0	0	30,939,150
126	Cash IN (အပ်ငွေ - ငွေသား)	2,222,000	8,070,000	1,852,000	385,000	2,720,000		1,953,000	566,000	188,000	3,589,000	725,000		1,714,000	2,702,000	2,142,000	310,000	332,000	1,494,000														30,964,000
127	ပိုငွေ / လိုငွေ	1,200	10,150	550	8,900	550	0	(150)	200	100	700	(500)	0	450	200	800	900	550	250	0	0	0	0	0	0	0	0	0	0	0	0	0	24,850
2023 - 2024

Cell Formulas
Range	Formula
B112:AF112	B112	=B113
G125,U125:AF125,M125	G125	=G114+G118-G117-G121-G122-G124
AG114:AG127	AG114	=SUM(B114:AF114)
B127:AF127	B127	=B126-B125

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B127:AF127	Cell Value	>=0	text	NO
B127:AF127	Cell Value	<0	text	NO
B112:AF112	Cell Value	beginning with "S"	text	NO

Sub Macro5()
'
' Macro5 Macro
'

'
    Windows("Daily Records (Nov 2023).xlsx").Activate
    Sheets("19.11.2023").Select
    Range("B26").Select
    Selection.Copy
    Windows("Yearly Records.xlsx").Activate
    Sheets("2023 - 2024").Select
    Range("T114").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Daily Records (Nov 2023).xlsx").Activate
    Range("C26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Yearly Records.xlsx").Activate
    Range("T115").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Daily Records (Nov 2023).xlsx").Activate
    Range("D26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Yearly Records.xlsx").Activate
    Range("T116").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Daily Records (Nov 2023).xlsx").Activate
    Range("E26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Yearly Records.xlsx").Activate
    Range("T117").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Daily Records (Nov 2023).xlsx").Activate
    Range("B36").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Yearly Records.xlsx").Activate
    Range("T118").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Daily Records (Nov 2023).xlsx").Activate
    Range("C36").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Yearly Records.xlsx").Activate
    Range("T119").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Daily Records (Nov 2023).xlsx").Activate
    Range("D36").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Yearly Records.xlsx").Activate
    Range("T120").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Daily Records (Nov 2023).xlsx").Activate
    Range("H12").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Yearly Records.xlsx").Activate
    Range("T121").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Daily Records (Nov 2023).xlsx").Activate
    Range("H18").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Yearly Records.xlsx").Activate
    Range("T122").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Daily Records (Nov 2023).xlsx").Activate
    Range("H24").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Yearly Records.xlsx").Activate
    Range("T123").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Daily Records (Nov 2023).xlsx").Activate
    Range("H32").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Yearly Records.xlsx").Activate
    Range("T124").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Daily Records (Nov 2023).xlsx").Activate
    Range("H34:H35").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Yearly Records.xlsx").Activate
    Range("T125").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

 

[eiloken]

I saw that you need to copy value of total to summary sheet and each row and column in summary sheet is permanent but how about daily recorder file? is toltal row of each value of it permanent too?

 

[chittunoo]

Hi eiloken,

Thanks for your reply.

- "Daily Record file" has "Sheets" named as each working date of a month. i.e. "19.11.2023", "20.11.2023", "22.11.2023", etc (without those off days).
- Each Sheets from "Daily Record file" is formatted the same. i.e. Data to copy from them is at specific cells and it is same cell locations for all Sheets.
- "Summary file" or "Yearly Record file" has "Sheets" named as fiscal year. i.e. "2023 - 2024", "2022 - 2023", etc
- Each Sheets from "Summary file" has Monthly Tables with columns as each day of that months. i.e. October 2023 table with 1-Oct, 2-Oct, below that table is November 2023 table with 1-Nov, 2-Nov, etc.
- Data copied from "Daily Record file" are to be pasted value at respective column (i.e. corresponding date)
e.g. Copied data from "19.11.2023" Sheet to be pasted value at respective cells at "November 2023" table at "19-Nov" column