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]

Will each "stock" sheet in the two workbooks have the same number of items?

 

[Maklil]

not always as you see sometimes there is item in file but not existed in another for instance in row 20,21 are existed in first file but not in the second also the row4 is existed in file2 but not in file1 .I hope understanding you and this what you mean.

 

[mumps]

Start by opening a new, blank workbook. Change the name of the first sheet to "Stock". Insert the following headers in row 1.

ITEM

BRAND

IMPORT

EXPORT

BALANCE

Copy/paste the macro below in a regular module. Change the folder path of the the two files (in red) and the file names (in blue) to suit your needs. Save the workbook as a macro-enabled file in a folder of your choice. Run the macro.

Sub CombineData()
    Application.ScreenUpdating = False
    Dim ws1 As Worksheet, ws2 As Worksheet, wb1 As Workbook, wb2 As Workbook, desWB As Workbook, desWS As Worksheet
    Dim v1 As Variant, v2 As Variant, i As Long, srcRng As Range, lRow As Long, fnd As Range
    Set desWB = ThisWorkbook
    Set desWS = ThisWorkbook.Sheets("Stock")
    Set wb1 = Workbooks.Open("C:\Test\STOCK.xlsx")
    Set ws1 = Sheets("Stock")
    Set wb2 = Workbooks.Open("C:\Test\STO.xlsx")
    Set ws2 = Sheets("Stock")
    v1 = ws1.Range("B2", ws1.Range("B" & Rows.Count).End(xlUp)).Resize(, 3).Value
    v2 = ws2.Range("B2", ws2.Range("B" & Rows.Count).End(xlUp)).Resize(, 3).Value
    Set srcRng = ws2.Range("B2", ws2.Range("B" & Rows.Count).End(xlUp))
    For i = LBound(v1) To UBound(v1)
        If Not IsError(Application.Match(v1(i, 1), srcRng, 0)) Then
            x = Application.Match(v1(i, 1), srcRng, 0)
            With desWS
                lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
                .Range("B" & lRow).Resize(, 3).Value = Array(v1(i, 1), v1(i, 2) + ws2.Range("C" & x + 1), v1(i, 3) + ws2.Range("D" & x + 1))
                .Range("E" & lRow) = .Range("C" & lRow) - .Range("D" & lRow)
            End With
        Else
            With desWS
                lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
                .Range("B" & lRow).Resize(, 3).Value = Array(v1(i, 1), v1(i, 2), v1(i, 3))
                .Range("E" & lRow) = .Range("C" & lRow) - .Range("D" & lRow)
            End With
        End If
    Next i
    For i = LBound(v2) To UBound(v2)
        Set fnd = desWS.Range("B:B").Find(v2(i, 1), LookIn:=xlValues, lookat:=xlWhole)
        If fnd Is Nothing Then
            With desWS
                lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
                .Range("B" & lRow).Resize(, 3).Value = Array(v2(i, 1), v2(i, 2), v2(i, 3))
                .Range("E" & lRow) = .Range("C" & lRow) - .Range("D" & lRow)
            End With
        End If
    Next i
    With desWS.Range("A2")
        .Value = "1"
        .AutoFill Destination:=Range("A2").Resize(Range("B" & Rows.Count).End(xlUp).Row - 1), Type:=xlFillSeries
    End With
    Application.DisplayAlerts = False
    desWB.SaveAs Filename:=desWB.Path & Application.PathSeparator & "Final Stock.xlsx", FileFormat:=51
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

 

[Maklil]

thanks . it gives error application defined error in this line

.AutoFill Destination:=Range("A2").Resize(Range("B" & Rows.Count).End(xlUp).Row - 1), Type:=xlFillSeries

and when run the macro every time should not repeat copying data have already existed to the bottom just replace data ( should delete data and copy from the beginning).

 

[mumps]

Insert this line of code:

desWS.UsedRange.Offset(1).ClearContents

directly below this line:

Set desWS = ThisWorkbook.Sheets("Stock")

I tested the macro using the data you posted and it worked properly without any error. Use the XL2BB add-in to post a copy of the sheet showing the result after you run the macro. Also, make sure that any cells that do not contain any values in columns C and D in the two "Stock" sheets, are blank.

 

[Maklil]

make sure that any cells that do not contain any values in columns C and D in the two "Stock" sheets, are blank.

yes . I can't contral the empty cell ( not always contains values somtimes should be blank)

 

[mumps]

If some cells in the Import and Export columns have no value then those cells must be blank or contain a zero. If they contain anything else, an error will be generated.

 

[Maklil]

do you mean there is no way to avoid blank cells?
what if create "-" for any cells by code to avoid this problem. is it possible?
look the problem seems just from the serial numbers in column A . this is what I got after running

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

 

[mumps]

I can't reproduce the problem. Could you upload a copy of your file (de-sensitized if necessary) to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.