calculate data from multiple sheets from workbook to another based on sheets name

hi experts
I have file DATA contains 4 sheets what I want importing data to file OUTPUT based on sheets name so every duplicated data are existed each sheet in file DATA should merge and summing when copy to FILE OUTPUT and if there are new data in sheets IMPORT & RETURNS SALES in FILE DATA should add to the bottom in FILE OUTPUT the result should show in file OUTPUT in sheet RESULT it should brings the data and merged and summing and matched between two files in COLS B,C,D if there are existed in FILE OUPUT based on sheets names , the headers are the same sheets names then should bring the data and calculate in last column BALANCE see the sheet original how was and sheet result how become
thanks in advance

Here is a possible solution: The names did not match in the original files. Corrections have been made in the attached files.

Sub collectdata()
Dim FileToOpen$, k&, i&, j&, jn$, cl&, jnc$, c&
Dim DataWb As Workbook, OutWB As Workbook
Dim ws As Worksheet, ou As Worksheet
Dim a, b
Dim dict As Object
Dim rcl As Range

Set OutWB = ThisWorkbook
FileToOpen = ThisWorkbook.Path & "\" & "DATA-calculate data from multiple.xlsx"
Set DataWb = Workbooks.Open(Filename:=FileToOpen, ReadOnly:=True)
Set dict = CreateObject("Scripting.Dictionary")
Set ou = OutWB.Sheets("ORGINAL")
With CreateObject("Scripting.Dictionary")
k = 1
b = ou.UsedRange.Value
For j = 2 To UBound(b)
    If Not .exists(b(j, 2) & b(j, 3) & b(j, 4)) Then
            jn = WorksheetFunction.Concat(b(j, 2), b(j, 3), b(j, 4))
            .Add jn, k
            k = k + 1
        End If
Next j
c = UBound(b) + 1
For Each ws In DataWb.Worksheets
    a = ws.UsedRange
    
    For i = 2 To UBound(a)
        Set rcl = OutWB.Sheets("ORGINAL").[E1:I2].Find(Trim(ws.Name), , xlValues, xlPart)
        cl = rcl.Column
        jnc = WorksheetFunction.Concat(a(i, 2), a(i, 3), a(i, 4))
        If .exists(jnc) Then
            ou.Cells(i, cl).Value = a(i, 5)
            ou.Cells(i, 10).FormulaR1C1 = "=RC[-5]+RC[-4]-RC[-3]-RC[-2]+RC[-1]"
        Else
            ou.Cells(c, 2).Value = a(i, 2)
            ou.Cells(c, 3).Value = a(i, 3)
            ou.Cells(c, 4).Value = a(i, 4)
            ou.Cells(c, cl).Value = a(i, 5)
            ou.Cells(c, 1).Value = ou.Cells(c - 1, 1).Value + 1
            ou.Cells(c, 10).FormulaR1C1 = "=RC[-5]+RC[-4]-RC[-3]-RC[-2]+RC[-1]"
            c = c + 1
        End If
        
    Next i
Next ws

End With
End Sub

thanks for your code and sorry about mistakes names
unfortunately it gives error values I put some comments inside the file I took the row2 and the highlighted the rows for example what's the problem
please check wrong result in sheet ORGINAL and compare with the right result in sheet RESULT

Here is some updated code. Let me know how it works.

Sub collectdata()
Dim FileToOpen$, k&, i&, j&, jn$, cl&, jnc$, c&, h&, nr&
Dim DataWb As Workbook, OutWB As Workbook
Dim ws As Worksheet, ou As Worksheet
Dim a, b()
Dim dict As Object
Dim rcl As Range
Application.ScreenUpdating = False
Set OutWB = ThisWorkbook
FileToOpen = ThisWorkbook.Path & "\" & "DATA-calculate data from multiple.xlsx"
Set DataWb = Workbooks.Open(Filename:=FileToOpen, ReadOnly:=True)
Set dict = CreateObject("Scripting.Dictionary")
Set ou = OutWB.Sheets("ORGINAL")
With CreateObject("Scripting.Dictionary")
k = 1
b = ou.UsedRange.Value
For j = 2 To UBound(b)
    If Not .exists(b(j, 2) & b(j, 3) & b(j, 4)) Then
            jn = WorksheetFunction.Concat(b(j, 2), b(j, 3), b(j, 4))
            .Add jn, k
            b(j, 10) = jn
            k = k + 1
        End If
Next j
c = ou.Cells(Rows.Count, 1).End(xlUp).Row + 1
For Each ws In DataWb.Worksheets
    a = ws.UsedRange
    Set rcl = ou.[E1:I2].Find(Trim(ws.Name), , xlValues, xlPart)
    cl = rcl.Column
    For i = 2 To ws.Cells(Rows.Count, 1).End(xlUp).Row 'UBound(a)
        jnc = WorksheetFunction.Concat(a(i, 2), a(i, 3), a(i, 4))
        If .exists(jnc) Then
            For h = LBound(b) To UBound(b)
                If jnc = b(h, 10) Then
                    nr = h
                End If
            Next h
            ou.Cells(nr, cl).Value = ou.Cells(nr, cl).Value + a(i, 5)
            ou.Cells(nr, 10).FormulaR1C1 = "=RC[-5]+RC[-4]-RC[-3]-RC[-2]+RC[-1]"
        Else
            ou.Cells(c, 2).Value = a(i, 2)
            ou.Cells(c, 3).Value = a(i, 3)
            ou.Cells(c, 4).Value = a(i, 4)
            ou.Cells(c, cl).Value = ou.Cells(c, cl).Value + a(i, 5)
            ou.Cells(c, 1).Value = ou.Cells(c - 1, 1).Value + 1
            b = Application.Transpose(b)
            ReDim Preserve b(1 To 10, 1 To c)
            b = Application.Transpose(b)
            b(c, 10) = jnc
            c = c + 1
            .Add jnc, k
            k = k + 1
        End If
        h = 0
    Next i
Next ws
End With
DataWb.Close False
ou.Range("J2:J" & c - 1).FormulaR1C1 = "=RC[-5]+RC[-4]-RC[-3]-RC[-2]+RC[-1]"
With ou.Range(Cells(2, 1), Cells(c - 1, 10))
    .HorizontalAlignment = xlCenter
    .Borders(7).LineStyle = xlContinuous
    .Borders(7).Weight = xlThin
    .Borders(8).LineStyle = xlContinuous
    .Borders(8).Weight = xlThin        '
    .Borders(9).LineStyle = xlContinuous
    .Borders(9).Weight = xlThin
    .Borders(10).LineStyle = xlContinuous
    .Borders(10).Weight = xlThin
    .Borders(11).LineStyle = xlContinuous
    .Borders(11).Weight = xlThin
    .Borders(12).LineStyle = xlContinuous
    .Borders(12).Weight = xlThin
End With
Application.ScreenUpdating = True
End Sub

thanks
but it gives error " object variable or with block variable not set" in this line

HTML Code: 

Don't understand the logic of row 11,12 in your result.
Assuming DATA.xlsx is in the same folder.
DO NOT open DATA.xslx, if open, close it first.

Sub test()
    Dim a, myDir As String, fn As String, i As Long, ii As Long, iii As Long
    Dim cn As Object, rs As Object, x, txt As String
    myDir = ThisWorkbook.Path & "\"
    fn = "Data.xlsx"
    a = ThisWorkbook.Sheets("ORGINAL").Cells(1).CurrentRegion.Value
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    With cn
        .Provider = "Microsoft.Ace.OLEDB.12.0"
        .Properties("Extended Properties") = "Excel 12.0;HDR=No;"
        .Open myDir & "\" & fn
    End With
    For ii = 6 To UBound(a, 2) - 1
        rs.Open "Select F2 & '" & Chr(2) & "' &  F3 & '" & Chr(2) & "' & F4, Sum(F5) From `" & _
                a(1, ii) & "$A2:E1000` Where F1 Is Not Null Group By F2, F3, F4;", cn, 3
        x = rs.GetRows: rs.Close
        For i = 2 To UBound(a, 1)
            txt = Join(Array(a(i, 2), a(i, 3), a(i, 4)), Chr(2))
            For iii = 0 To UBound(x, 2)
                If x(0, iii) = txt Then
                    a(i, ii) = x(1, iii): Exit For
                End If
            Next
        Next
    Next
    With ThisWorkbook.Sheets("ORGINAL").Cells(1).CurrentRegion
        .Resize(, .Columns.Count - 1).Value = a
    End With
End Sub

@Jindon thanks for your code

Don't understand the logic of row 11,12 in your result.

I said this

if there are new data in sheets IMPORT & RETURNS SALES in FILE DATA should add to the bottom in FILE OUTPUT

it doesn't seem that clear
see in sheet IMPORT & RETURNS SALES data are new should copy to bottom , it is are not existed in file output to match

Originally Posted by MKLAQ

if there are new data in sheets IMPORT & RETURNS SALES in FILE DATA should add to the bottom in FILE OUTPUT the result should show in file OUTPUT in sheet RESULT

What is "RETURNS SALES"?

@Jindon I really sorry I made mistake the right is RETURNS EXPORT

Sub test()
    Dim a, b, myDir As String, fn As String, i As Long, ii As Long, iii As Long
    Dim cn As Object, rs As Object, x, y, txt As String, n As Long, t As Long, myRows As Long
    myDir = ThisWorkbook.Path & "\"
    fn = "Data.xlsx"
    a = ThisWorkbook.Sheets("ORGINAL").Cells(1).CurrentRegion.Value
    ReDim b(1 To 10000, 1 To UBound(a, 2) - 1)
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    With cn
        .Provider = "Microsoft.Ace.OLEDB.12.0"
        .Properties("Extended Properties") = "Excel 12.0;HDR=No;"
        .Open myDir & "\" & fn
    End With
    For ii = 6 To UBound(a, 2) - 1
        rs.Open "Select F2 & '" & Chr(2) & "' &  F3 & '" & Chr(2) & "' & F4, Sum(F5) From `" & _
                a(1, ii) & "$A2:E1000` Where F1 Is Not Null Group By F2, F3, F4;", cn, 3
        myRows = rs.RecordCount: x = rs.GetRows: rs.Close: n = 0
        For i = 2 To UBound(a, 1)
            txt = Join(Array(a(i, 2), a(i, 3), a(i, 4)), Chr(2))
            For iii = 0 To UBound(x, 2)
                If x(0, iii) = txt Then
                    n = n + 1: a(i, ii) = x(1, iii): x(0, iii) = "": Exit For
                End If
            Next
        Next
        If (n < myRows) * ((ii = 6) + (ii = 9)) Then
            For i = 0 To UBound(x, 2)
                If x(0, i) <> "" Then
                    t = t + 1: y = Split(x(0, i), Chr(2))
                    b(t, 1) = "=r[-1]c+1": b(t, 2) = y(0): b(t, 3) = y(1)
                    b(t, 4) = y(2): b(t, IIf(ii = 6, 5, ii)) = x(1, i)
                End If
            Next
        End If
    Next
    With ThisWorkbook.Sheets("ORGINAL").Cells(1).CurrentRegion
        .Resize(, .Columns.Count - 1).Value = a
        If t > 0 Then
            .Rows(2).Copy
            With .Rows(.Rows.Count + 1).Resize(t)
                .PasteSpecial xlPasteFormats
                .Value = b
                .Columns(.Columns.Count).FormulaR1C1 = _
                "=sum(rc[-5]:rc[-4],-rc[-3],-rc[-2],rc[-1])"
            End With
        End If
        .Cells(1).Select
    End With
End Sub

@Jindon that's great just I would hide the formula in last column it should show like value and show this mark "-" for any empty cell

Sub test()
    Dim a, b, myDir As String, fn As String, i As Long, ii As Long, iii As Long
    Dim cn As Object, rs As Object, x, y, txt As String, n As Long, t As Long, myRows As Long
    myDir = ThisWorkbook.Path & "\"
    fn = "Data.xlsx"
    a = ThisWorkbook.Sheets("ORGINAL").Cells(1).CurrentRegion.Value
    ReDim b(1 To 10000, 1 To UBound(a, 2) - 1)
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    With cn
        .Provider = "Microsoft.Ace.OLEDB.12.0"
        .Properties("Extended Properties") = "Excel 12.0;HDR=No;"
        .Open myDir & "\" & fn
    End With
    For ii = 6 To UBound(a, 2) - 1
        rs.Open "Select F2 & '" & Chr(2) & "' &  F3 & '" & Chr(2) & "' & F4, Sum(F5) From `" & _
                a(1, ii) & "$A2:E1000` Where F1 Is Not Null Group By F2, F3, F4;", cn, 3
        myRows = rs.RecordCount: x = rs.GetRows: rs.Close: n = 0
        For i = 2 To UBound(a, 1)
            txt = Join(Array(a(i, 2), a(i, 3), a(i, 4)), Chr(2))
            For iii = 0 To UBound(x, 2)
                If x(0, iii) = txt Then
                    n = n + 1: a(i, ii) = x(1, iii): x(0, iii) = "": Exit For
                End If
            Next
        Next
        If (n < myRows) * ((ii = 6) + (ii = 9)) Then
            For i = 0 To UBound(x, 2)
                If x(0, i) <> "" Then
                    t = t + 1: y = Split(x(0, i), Chr(2))
                    b(t, 1) = "=r[-1]c+1": b(t, 2) = y(0): b(t, 3) = y(1)
                    b(t, 4) = y(2): b(t, IIf(ii = 6, 5, ii)) = x(1, i)
                End If
            Next
        End If
    Next
    With ThisWorkbook.Sheets("ORGINAL").Cells(1).CurrentRegion
        .Value = a
        If t > 0 Then
            .Rows(2).Copy
            With .Rows(.Rows.Count + 1).Resize(t)
                .PasteSpecial xlPasteFormats
                .Value = b
                .Columns(.Columns.Count).FormulaR1C1 = _
                "=sum(rc[-5]:rc[-4],-rc[-3],-rc[-2],rc[-1])"
            End With
        End If
        With .Offset(1).Resize(.Rows.Count + t - 1)
            .Columns(.Columns.Count).FormulaR1C1 = "=sum(rc[-5]:rc[-4],-rc[-3],-rc[-2],rc[-1])"
            On Error Resume Next
            .SpecialCells(4).Value = 0
            On Error GoTo 0
            .Value = .Value
            .NumberFormat = "0;-0;-;@"
        End With
    End With
End Sub

@Jindon thanks so much for a great help

Here is the file with the code. That error presents if the sheet name does not match with the header rows.

@maniacb thanks for follow the problem and fix it
many thanks