VBA to search for different valuse in entire workbook depending on texts

[klausm]

Hi everyone
I have a workbook with differnt sheets and different data and values and a master sheet.
I need to search for all different values in the entire workbook for available worksheets and future sheets also, after finding the values according to the criteria in master sheet i want the values all to be added in new column near each criteria. I was using before sumif formula but now it is very hard since i am having over 50 sheets and over 300 different criteria. Can you please help me with VBA code for that issue.
Attached is an exmple of what I mean.
Thanks for the help

[jindon]

Try


Sub test()
Dim a, b, i&, wsM As Worksheet, ws As Worksheet, r As Range
Set wsM = Sheets("Master sheet ")
a = wsM.Range("b1", wsM.Range("b" & Rows.Count).End(xlUp)).Resize(, 2).Value2
ReDim b(1 To UBound(a, 1), 1 To 1)
For i = 2 To UBound(a, 1)
For Each ws In Worksheets
If Not ws Is wsM Then
Set r = Intersect(ws.UsedRange, ws.Columns("a:g"))
b(i, 1) = b(i, 1) + ws.Evaluate("sumif(" & r.Columns("b").Address & _
",""" & a(i, 1) & """," & r.Columns("g").Address & ")")
End If
Next
Next
wsM.[e1].Resize(UBound(b, 1)) = b
End Sub

[klausm]

Many thanks for the help it works after preparing the correct file for that purpose.