modified code peter_SSs (copy multiple columns across multiple sheets and calculate)

[abdo meghari]

hi experts
after long time I've found this thread copy multiple columns and matched from multiple sheets to sheet summary and calculate the code for mr @Peter_SSs and it 's soon from my requirements . I hope from him or any body has knowladge to mod the code .

Sub CollateData_v2()
  Dim d As Object
  Dim ShList As Variant, a As Variant, vals As Variant
  Dim i As Long, j As Long
  Dim s As String
 
  Set d = CreateObject("Scripting.Dictionary")
  ShList = Split("stock|sales|pur|returns", "|")
  For j = 0 To UBound(ShList)
    With Sheets(ShList(j))
      a = .UsedRange.Value2
      For i = 2 To UBound(a)
        s = Join(Application.Index(a, i, Array(2, 3, 4)), ";")
        If Len(s) > 2 Then
          If Not d.exists(s) Then d(s) = ";;;"
          vals = Split(d(s), ";")
          vals(j) = a(i, 5)
          d(s) = Join(vals, ";")
        End If
      Next i
    End With
  Next j
  Application.ScreenUpdating = False
  With Sheets("summary")
    .UsedRange.EntireRow.Delete
    With .Range("B2:C2").Resize(d.Count)
      .Value = Application.Transpose(Array(d.Keys, d.Items))
      With .Columns(2)
        .TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=False, Semicolon:=True, Comma:=False, Space:=False, Other:=False
        With .Offset(, 4)
          .FormulaR1C1 = "=RC[-4]-RC[-3]+RC[-2]+RC[-1]"
          .Value = .Value
        End With
        .Resize(, 2).EntireColumn.Insert
      End With
      .Columns(1).TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=False, Semicolon:=True, Comma:=False, Space:=False, Other:=False
      With .Columns(0)
        .Cells(1).Value = 1
        .DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1
      End With
    End With
    With .Range("A1:I1")
      .Value = Array("item", "BRAND", "TYPE", "MONAFACTURE", "STOCK", "SALES", "PUR", "RETURNS", "BALANCE")
      .Font.Bold = True
  .Interior.Color = RGB(166, 166, 166)
      .EntireColumn.AutoFit
    End With
    With .UsedRange
      .BorderAround LineStyle:=xlContinuous
      .Borders(xlInsideVertical).LineStyle = xlContinuous
      .Borders(xlInsideHorizontal).LineStyle = xlContinuous
    End With
  End With
  Application.ScreenUpdating = True
End Sub

so I have many data almost the same structure the expected result in sheet INVENTORY should create whole data (headers,borders,formatting)
as you see the sheets(buying,selling,selling return,buying return) contain repeated data but different dates except sheet STOCK doesn't duplicates. when merge duplicates items should be based on COL B and summing values just column contain QTY across multiple sheets with note the duplicate item repeate for all sheets except sheet STOCK . about sheet INVENTORY QTY I put
the formula how should calculatein COL K . as to buying price , selling price should calculate average as show in sheet INVENTORY in column L,M based on only sheets(stock,buying , selling ) .about column NET calculates basd on COL(K,L,M) i put the formula how should calculate with considering when run macro repeatedly should replace the data in sheet inventory like updating
last thing I have two notes . first the formulas should show as value. second my data are big always are from 4000: 6000 rows across multiple sheets . but this is just simple data to ubderstand me
note: when create data in sheet INVENTORY should be based on sheets names (stock,buying,selling,selling returns,buying returns)

PRICE.xlsm
	A	B	C	D	E	F	G	H
1	ITEM	BATCH	BRAND	TYPE	ORIGIN	QTY	BYUING PRICE	SELLING PRICE
2	1	BSJ-1200G1	1200R20	G580	JAP	220.00	$425.00	$450.00
3	2	BSJ-1200G2	1200R20	G580	JAP	300.00	$422.00	$455.00
4	3	BSJ-1200G3	1200R20	R187	JAP	350.00	$430.00	$456.00
5	4	BSJ-1200G4	1200R20	R187	THI	200.00	$430.00	$444.00
6	5	BSJ-1224G5	1200R24	G580	JAP	140.00	$650.00	$700.00
7	6	BSJ-1400V1	1400R20	VSJ	JAP	140.00	$780.00	$850.00
8	7	BSJ-1400V2	1400R20	R180	JAP	-	$880.00	$890.00
STOCK

PRICE.xlsm
	A	B	C	D	E	F	G	H	I	J
1	DATE	BATCH	CUSTOMER NO	INVOICE NO	BRAND	TYPE	ORIGIN	QTY	BYUING PRICE	TOTAL
2	02/01/2021	BSJ-1200G1	ALL-BST1-00	IN-BSJT/2-00	1200R20	G580	JAP	2,000.00	$423.00	$846,000.00
3	02/02/2021	BSJ-1200G1	ALL-BST1-01	IN-BSJT/2-01	1200R20	G580	JAP	100.00	$433.00	$43,300.00
4	02/03/2021	BSJ-1200G1	ALL-BST1-02	IN-BSJT/2-02	1200R20	G580	JAP	50.00	$430.00	$21,500.00
5	02/04/2021	BSJ-1200G4	ALL-BST1-01	IN-BSJT/2-01	1200R20	R187	THI	120.00	$433.00	$51,960.00
6	02/05/2021	BSJ-1224G5	ALL-BST1-02	IN-BSJT/2-02	1200R24	G580	JAP	300.00	$600.00	$180,000.00
7	02/06/2021	BSJ-1400V1	ALL-BST1-02	IN-BSJT/2-02	1400R20	VSJ	JAP	20.00	$770.00	$15,400.00
8	02/07/2021	BSJ-1400V2	ALL-BST1-03	IN-BSJT/2-03	1400R20	R180	JAP	200.00	$800.00	$160,000.00
9	02/08/2021	BSJ-1400V2	ALL-BST1-04	IN-BSJT/2-04	1400R20	R180	JAP	100.00	$820.00	$82,000.00
BUYING

Cell Formulas
Range	Formula
J2:J9	J2	=I2*H2

PRICE.xlsm
	A	B	C	D	E	F	G	H	I	J
1	DATE	BATCH	CUSTOMER NO	INVOICE NO	BRAND	TYPE	ORIGIN	QTY	SELLING PRICE	TOTAL
2	03/01/2021	BSJ-1200G1	CCSL-BST1-00	IN-BSJT/2-00	1200R20	G580	JAP	100.00	$470.00	$47,000.00
3	03/02/2021	BSJ-1200G1	CCSL-BST1-01	IN-BSJT/2-01	1200R20	G580	JAP	20.00	$475.00	$9,500.00
4	03/03/2021	BSJ-1200G1	CCSL-BST1-02	IN-BSJT/2-02	1200R20	G580	JAP	30.00	$465.00	$13,950.00
5	04/04/2021	BSJ-1200G4	CCSL-BST1-01	IN-BSJT/2-01	1200R20	R187	THI	50.00	$466.00	$23,300.00
6	04/05/2021	BSJ-1224G5	CCSL-BST1-02	IN-BSJT/2-02	1200R24	G580	JAP	60.00	$710.00	$42,600.00
SALLING

Cell Formulas
Range	Formula
J2:J6	J2	=I2*H2

PRICE.xlsm
	A	B	C	D	E	F	G	H	I	J
1	DATE	BATCH	CUSTOMER NO	INVOICE NO	BRAND	TYPE	ORIGIN	QTY	BYUING PRICE	TOTAL
2	05/01/2021	BSJ-1200G1	RRSL-BST1-00	IN-BSJT/2-00	1200R20	G580	JAP	10.00	$470.00	$4,700.00
3	05/02/2021	BSJ-1200G1	RRSL-BST1-01	IN-BSJT/2-01	1200R20	G580	JAP	20.00	$475.00	$9,500.00
4	05/03/2021	BSJ-1200G1	RRSL-BST1-02	IN-BSJT/2-02	1200R20	G580	JAP	15.00	$475.00	$7,125.00
SALLING RETURN

Cell Formulas
Range	Formula
J2:J4	J2	=I2*H2

PRICE.xlsm
	A	B	C	D	E	F	G	H	I	J
1	DATE	BATCH	CUSTOMER NO	INVOICE NO	BRAND	TYPE	ORIGIN	QTY	BYUING PRICE	TOTAL
2	05/03/2021	BSJ-1200G1	RRB-BST1-02	IN-BSJT/2-02	1200R20	G580	JAP	10.00	$423.00	$4,230.00
3	05/04/2021	BSJ-1400V2	RRB-BST1-03	IN-BSJT/2-03	1400R20	R180	JAP	20.00	$800.00	$16,000.00
4	05/05/2021	BSJ-1400V2	RRB-BST1-04	IN-BSJT/2-04	1400R20	R180	JAP	20.00	$800.00	$16,000.00
BUYING RETURN

Cell Formulas
Range	Formula
J2:J4	J2	=I2*H2

expected result

PRICE.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	ITEM	BATCH	BRAND	TYPE	ORIGIN	STOCK	BUYING	SALLING	SALLING RETURN	BUYING RETURN	QTY	BUYING PRICE	SALLING PRICE	NET
2	1	BSJ-1200G1	1200R20	G580	JAP	220.00	2,150.00	150.00	45.00	10.00	2,255.00	$427.75	$465.00	$83,998.75
3	2	BSJ-1200G2	1200R20	G580	JAP	300.00	-	-	-	-	300.00	$422.00	$455.00	$9,900.00
4	3	BSJ-1200G3	1200R20	R187	JAP	350.00	-	-	-	-	350.00	$430.00	$456.00	$9,100.00
5	4	BSJ-1200G4	1200R20	R187	THI	200.00	120	50	-	-	270.00	$431.50	$455.00	$6,345.00
6	5	BSJ-1224G5	1200R24	G580	JAP	140.00	300	60	-	-	380.00	$625.00	$705.00	$30,400.00
7	6	BSJ-1400V1	1400R20	VSJ	JAP	140.00	20	-	-	-	160.00	$775.00	$850.00	$12,000.00
8	7	BSJ-1400V2	1400R20	R180	JAP	-	300	-	-	40	260.00	$810.00	$890.00	$20,800.00
INVENTORY

Cell Formulas
Range	Formula
K2:K8	K2	=F2+G2-H2+I2-J2
N2:N8	N2	=(M2-L2)*K2

the stars shows in images are short word JAPAN(JAP) but it shows *** when use XL2BB
I hope some body help