summing values for repeated data in all sheets

Leopard

I've taken a different (=easier) approach to create your summary. Rather than populate tables in the SUMMARY, I've just summarised the sheets by rows (with the quantities in each sheet added together- note that I changed the headings from Balance to Quantity as per your Discussion point, and Total Quantity in SUMMARY).

On change of D1, the macro first clears the summary sheet (after row 4) then loops through the sheets adding values in the next free row SumNext (which is detected for the worksheet). The quanties per sheet are added via the second line in bold below):

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address(0, 0) <> "D1" Then Exit Sub
Application.EnableEvents = False

Dim ws As Worksheet, Rg As Range, SumNext As Integer

Range("A4:F20").Clear ' clear results

   For Each ws In Sheets(Array("IMP", "EX", "PURRET", "SELRET"))
   SumNext = Cells(Rows.Count, 1).End(xlUp).Row + 1
   Cells(SumNext, 1) = ws.Name
   With ws
      Set Rg = .Range("B2:B" & .Cells(Rows.Count, 1).End(xlUp).Row)
        Cells(SumNext, 2) = Target.Value ' start a row (even if no quantities are found)
        For Each Cell In Rg
            If Cell.Value = Target.Value Then
             For n = 1 To 3
                Cells(SumNext, n + 2) = Cell.Offset(0, n).Value
             Next n
             Cells(SumNext, 6) = Cells(SumNext, 6) + Cell.Offset(0, 4).Value
            End If
        Next Cell
    End With
    Next ws

Cells(SumNext + 1, 1) = "Nett"
Cells(SumNext + 1, 6).Formula = "=F4-F5-F6+F7"

With Range("A8:F8")
    .Font.Bold = True
    .Font.Size = 14
    .Font.Color = vbWhite
    .Interior.Color = vbBlue
End With

Application.EnableEvents = True
End Sub

Please note that in the revised file attached, your data validation appears (transposed) in row 1 of SUMMARY (but the column are hidden).

Note to that Application.EnableEvents are disabled  (to allow SUMMARY to be modified without triggering the WorksheetChange event) then re-enabled at the end of the sub.

Hope this works for you.