Sub GLL()
Dim Arr(), vlArr(), I, J, K, DK1, DK2, Ws, lr, x, y
With Sheet1
lr = .Range("A" & Rows.Count).End(3).Row
Arr = .Range("A3:I" & lr).Value
End With
Ws = Array(Sheet2, Sheet3)
For J = 0 To UBound(Ws)
With Ws(J)
K = 0: x = 0: y = 0
ReDim vlArr(1 To UBound(Arr, 1), 1 To 5)
DK1 = WorksheetFunction.SumIf(Sheet1.Range("C4:C" & lr), .Name, Sheet1.Range("H4:H" & lr)) * 0.1
DK2 = WorksheetFunction.SumIf(Sheet1.Range("C4:C" & lr), .Name, Sheet1.Range("I4:I" & lr)) * 0.1
For I = 1 To UBound(Arr, 1)
If Arr(I, 1) <> Empty And Arr(I, 3) = .Name And _
IIf(J = 0, (Arr(I, 4) >= DK1 Or Arr(I, 8) >= DK1), (Arr(I, 5) >= DK2 Or Arr(I, 9) >= DK2)) Then
K = K + 1
vlArr(K, 1) = Arr(I, 1)
vlArr(K, 2) = Arr(I, 2)
vlArr(K, 3) = "'" & Arr(I, 3)
vlArr(K, 4) = Arr(I, 4 + J)
vlArr(K, 5) = Arr(I, 8 + J)
x = x + vlArr(K, 4)
y = y + vlArr(K, 5)
End If
Next
.[A3:E10000].ClearContents
.[A3].Resize(K, 5) = vlArr
.[A3].Offset(K) = "Other"
.[B3].Offset(K) = "Other Clients"
.[C3].Offset(K) = "'" & .Name
.[B3].Offset(K + 1) = "Total"
.[D3].Offset(K + 1) = WorksheetFunction.SumIf(Sheet1.Range("C4:C" & lr), .Name, Sheet1.Range("D4:D" & lr).Offset(, J))
.[E3].Offset(K + 1) = WorksheetFunction.SumIf(Sheet1.Range("C4:C" & lr), .Name, Sheet1.Range("H4:H" & lr).Offset(, J))
.[D3].Offset(K) = .[D3].Offset(K + 1) - x
.[E3].Offset(K) = .[E3].Offset(K + 1) - y
End With
Next
End Sub