combined the same sheet name between two files & save as xlsx

[Maklil]

Hi experts,
I search for macro to combine the same sheet name between two files are in the same folder . so should combine based on match column B and summing values for columns C,D and insert column BALANCE in column E to subtract column C from column D and save the file as xlsx and the file name should be "FINAL STOCK" and sheet name should be "STOCK" based on sheet name into two files .
the sheet STOCK into two files is existed in last sheet .

STOCK.xlsx
	A	B	C	D
1	ITEM	BRAND	IMPORT	EXPORT
2	1	ELEC-1000	200
3	2	ELEC-1001	1200
4	3	ELEC-1002		122
5	4	ELEC-1003	1200
6	5	ELEC-1004	120
7	6	ELEC-1005	300
8	7	ELEC-1006	230
9	8	ELEC-1007	123
10	9	ELEC-1008	123
11	10	ELEC-1009	123
12	11	ELEC-1010	1122
13	12	ELEC-1011	122	10
14	13	ELEC-1012		4
15	14	ELEC-1013	123
16	15	ELEC-1014	123	1
17	16	ELEC-1015	11
18	17	ELEC-1016	11
19	18	ELEC-1017	12
20	19	ELEC-1018	12
21	20	ELEC-1019	10	12
stock

STO.xlsx
	A	B	C	D
1	ITEM	BRAND	IMPORT	EXPORT
2	1	ELEC-1012	123
3	2	ELEC-1013	10	10
4	3	ELEC-1022	20	12
5	4	ELEC-1009	11
6	5	ELEC-1010	10
7	6	ELEC-1011	12
8	7	ELEC-1003	220
9	8	ELEC-1004		4
10	9	ELEC-1005	100
11	10	ELEC-1006	10
12	11	ELEC-1007	10
13	12	ELEC-1008	10
14	13	ELEC-1000	10
15	14	ELEC-1001	10
16	15	ELEC-1002		22
17	16	ELEC-1014	11	2
18	17	ELEC-1015	112
19	18	ELEC-1016	120
20	19	ELEC-1017	10	2
stock

result (should be the same formatting & borders)

FINAL STOCK.xlsx
	A	B	C	D	E
1	ITEM	BRAND	IMPORT	EXPORT	BALANCE
2	1	ELEC-1000	210.00	-	210.00
3	2	ELEC-1001	1,210.00	-	1,210.00
4	3	ELEC-1002	-	144.00	-144.00
5	4	ELEC-1003	1,420.00	-	1,420.00
6	5	ELEC-1004	120.00	4.00	116.00
7	6	ELEC-1005	400.00	-	400.00
8	7	ELEC-1006	240.00	-	240.00
9	8	ELEC-1007	133.00	-	133.00
10	9	ELEC-1008	133.00	-	133.00
11	10	ELEC-1009	134.00	-	134.00
12	11	ELEC-1010	1,132.00	-	1,132.00
13	12	ELEC-1011	134.00	10.00	124.00
14	13	ELEC-1012	123.00	4.00	119.00
15	14	ELEC-1013	133.00	10.00	123.00
16	15	ELEC-1014	134.00	3.00	131.00
17	16	ELEC-1015	123.00	-	123.00
18	17	ELEC-1016	131.00	-	131.00
19	18	ELEC-1017	22.00	2.00	20.00
20	19	ELEC-1018	12.00	-	12.00
21	20	ELEC-1019	10.00	12.00	-2.00
22	21	ELEC-1022	20.00	12.00	8.00
STOCK

thanks

 

[mumps]

Replace the current part of the code with this:

With desWS
    .Range("A2").Value = "1"
    .Range("A2").AutoFill Destination:=.Range("A2").Resize(.Range("B" & .Rows.Count).End(xlUp).Row - 1), Type:=xlFillSeries
    .Cells(1, 1).Sort Key1:=Columns(2), Order1:=xlAscending, Orientation:=xlTopToBottom, Header:=xlYes
End With

 

[Maklil]

thanks again
I need arranging numbers sequences in column A
this is what I got

Final Stock.xlsx
	A	B	C	D	E
1	ITEM	BRAND	IMPORT	EXPORT	BALANCE
2	13	ELEC-1000	299.00	4.00	295.00
3	14	ELEC-1001	1,323.00	0.00	1,323.00
4	15	ELEC-1002	44.00	145.00	-101.00
5	7	ELEC-1003	1,476.00	-	1,476.00
6	8	ELEC-1004	208.00	4	204.00
7	9	ELEC-1005	1,244.00	-	1,244.00
8	10	ELEC-1006	513.00	10	503.00
9	11	ELEC-1007	256.00	4	252.00
10	12	ELEC-1008	577.00	0.00	577.00
11	4	ELEC-1009	257.00	2	255.00
12	5	ELEC-1010	2,377.00	2.00	2,375.00
13	6	ELEC-1011	490.00	20	470.00
14	1	ELEC-1012	123.00	4	119.00
15	2	ELEC-1013	133.00	10	123.00
16	16	ELEC-1014	354.00	23	331.00
17	17	ELEC-1015	123.00	10	113.00
18	18	ELEC-1016	241.00	0.00	241.00
19	19	ELEC-1017	242.00	12	230.00
20	20	ELEC-1018	232.00	15.00	217.00
21	21	ELEC-1019	610.00	12.00	598.00
22	22	ELEC-1020	34	20	14
23	23	ELEC-1021	120	10	110
24	3	ELEC-1022	54	12.00	42.00
25	24	ELEC-1023	220		220
stock

and this is should be

Final Stock.xlsx
	A	B	C	D	E
1	ITEM	BRAND	IMPORT	EXPORT	BALANCE
2	1	ELEC-1000	299.00	4.00	295.00
3	2	ELEC-1001	1,323.00	0.00	1,323.00
4	3	ELEC-1002	44.00	145.00	-101.00
5	4	ELEC-1003	1,476.00	-	1,476.00
6	5	ELEC-1004	208.00	4	204.00
7	6	ELEC-1005	1,244.00	-	1,244.00
8	7	ELEC-1006	513.00	10	503.00
9	8	ELEC-1007	256.00	4	252.00
10	9	ELEC-1008	577.00	0.00	577.00
11	10	ELEC-1009	257.00	2	255.00
12	11	ELEC-1010	2,377.00	2.00	2,375.00
13	12	ELEC-1011	490.00	20	470.00
14	13	ELEC-1012	123.00	4	119.00
15	14	ELEC-1013	133.00	10	123.00
16	15	ELEC-1014	354.00	23	331.00
17	16	ELEC-1015	123.00	10	113.00
18	17	ELEC-1016	241.00	0.00	241.00
19	18	ELEC-1017	242.00	12	230.00
20	19	ELEC-1018	232.00	15.00	217.00
21	20	ELEC-1019	610.00	12.00	598.00
22	21	ELEC-1020	34	20	14
23	22	ELEC-1021	120	10	110
24	23	ELEC-1022	54	12.00	42.00
25	24	ELEC-1023	220		220
stock

 

[mumps]

Try:

With desWS
    .Cells(1, 1).Sort Key1:=Columns(2), Order1:=xlAscending, Orientation:=xlTopToBottom, Header:=xlYes
    .Range("A2").Value = "1"
    .Range("A2").AutoFill Destination:=.Range("A2").Resize(.Range("B" & .Rows.Count).End(xlUp).Row - 1), Type:=xlFillSeries
End With

 

[Maklil]

all of things are great !
many thanks for your a great assistance

 

[mumps]

You are very welcome.