جرب هذا الكود
Option Explicit
Sub Give_Data()
Dim Dic As Object
Dim i%, Itm, k
Dim max_ro%, Laste_Row%
Dim Sh As Worksheet 'source_sheet (Main)
Dim Th As Worksheet ' Target_sheet (Repport)
Set Sh = Sheets("Main"): Set Th = Sheets("Repport")
max_ro = Th.Cells(Rows.Count, 1).End(3).Row
If max_ro = 1 Then max_ro = 2
Th.Range("a2:b" & max_ro).ClearContents
Laste_Row = Sh.Cells(Rows.Count, 1).End(3).Row
If Laste_Row < 2 Then
MsgBox "No Data"
Exit Sub
End If
Set Dic = CreateObject("Scripting.Dictionary")
With Dic
For i = 2 To Laste_Row
If Sh.Range("B" & i) <> vbNullString Then
k = Sh.Range("B" & i)
Itm = Sh.Range("D" & i) - Sh.Range("C" & i)
If Not .Exists(k) Then
.Add k, Itm
Else
Dic(k) = Dic(k) + Itm
End If
End If
Next i
Th.Range("A2").Resize(.Count, 1) = Application.Transpose(.keys)
Th.Range("B2").Resize(.Count, 1) = Application.Transpose(.Items)
End With
'===============
Dic.RemoveAll: Set Dic = Nothing
End Sub
الملف مرفق
Exemple.xlsm