using dictionary to brings data based on word in column

[abdelfattah]

Hi,
I search for the internet to brings data across sheets to MASTER sheet based on word "CCV" in column B across sheets.
and I got code and adapted based on my requirements ,but the only thing happens just copy from last sheet and ignores the others. when try run the code every time should delete data in MASTER sheet before brings data. I would do it by dictionary, not else way.

so I put expected result in MASTER sheet
thanks

[arnelgp]

see if this is what you want.

[abdelfattah]

Hi,
first thanks
second this is not what I want
third I would by dictionary as I mentioned.

I would do it by dictionary, not else way.

[jindon]

Sub test()
Dim ws, a, i&, s$, w, dic As Object
Set dic = CreateObject("Scripting.Dictionary")
For Each ws In Worksheets
If ws.Name <> "MASTER" Then
a = ws.Cells(1).CurrentRegion.Value
For i = 2 To UBound(a, 1)
If a(i, 2) = "CCV" Then
s = Join(Array(a(i, 1), a(i, 3)), Chr(2))
If Not dic.exists(s) Then
ReDim w(1 To 4)
w(1) = a(i, 1): w(2) = a(i, 2): w(3) = a(i, 3)
Else
w = dic(a(i, 2))
End If
w(4) = w(4) + a(i, 4): dic(s) = w
End If
Next
End If
Next
With Sheets("MASTER").Cells(1).CurrentRegion
.Offset(1).ClearContents
If dic.Count Then .Rows(2).Resize(dic.Count).Value = Application.Index(dic.Items, 0, 0)
End With
End Sub

[abdelfattah]

thanks jindon !
this is awesome!:clap: