using dictionary to brings data based on word in column

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

see if this is what you want.

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

Quote:

---

I would do it by dictionary, not else way.

---

Code:

---

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

---

thanks jindon !
this is awesome!:clap: