Applying SUMIF Formula in Multiple Sheet with VBA

[Zachary14]

Hi guys, i need some help. maybe you can help. i have 4 sheets, first sheet as a Master Sheet. second until fourth sheet act as a slave sheet. Whenever there are any change in "sold" column in slave sheets, it will fill and affect the amount in "sold" 's column in Master Sheet based on criteria in "ID" Column. I'm using sumif formula written in VBA, the problem is when the program executed, it wasn't work. The amount in "sold" column in master sheet didn't change, it's just affect first row and only blinking. Here's the code :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim shops As Variant
Dim i as integer, j as integer, sale_result As Integer
Dim julat, jumlah
shops = Array("North_Shop", "South_Shop", "West_Shop")
For i = LBound(shops) To UBound(shops)
Set julat = Worksheets(shops(i)).Range("a6:a12")
Set jumlah = Worksheets(shops(i)).Range("c6:c12")
For j = 6 To 10
sale_result = Application.WorksheetFunction.SumIf(julat, Worksheets("Master_Shop").Cells(j, 1), jumlah)
Cells(j, 4).Value = sale_result
Next j
Next i
End Sub

and here's the sheet.
i'm noob in vba, i just really need help guys. thank you

 

[bebo021999]

It requires 3 worksheet_change events for each slave sheet.
Right click on "North_Shop" tab name then paste below code. Repeat same action for next 2 slave sheets.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim dic As Object
Dim lr&, i&, ws As Worksheet, rng, arr(), key
Set dic = CreateObject("Scripting.dictionary")
If Intersect(Target, Columns("C")) Is Nothing Then Exit Sub
    For Each ws In Sheets
        If "North_ShopSouth_ShopWest_Shop" Like "*" & ws.Name & "*" Then
            lr = ws.Cells(Rows.Count, "A").End(xlUp).Row
            rng = ws.Range("A6:C" & lr).Value
            For i = 1 To UBound(rng)
                If Not dic.exists(rng(i, 1)) Then
                    dic.Add rng(i, 1), rng(i, 3)
                Else
                    dic(rng(i, 1)) = dic(rng(i, 1)) + rng(i, 3)
                End If
            Next
        End If
    Next
    With Worksheets("Master_Shop")
        lr = .Cells(Rows.Count, "A").End(xlUp).Row
        rng = .Range("A6:A" & lr).Value
        ReDim arr(1 To lr - 5, 1 To 1)
        For i = 1 To lr - 5
            For Each key In dic.keys
                If key = rng(i, 1) Then arr(i, 1) = dic(key)
            Next
        Next
        .Range("D6").Resize(lr - 5, 1).Value = arr
    End With
End Sub

 

[Zachary14]

It requires 3 worksheet_change events for each slave sheet.
Right click on "North_Shop" tab name then paste below code. Repeat same action for next 2 slave sheets.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim dic As Object
Dim lr&, i&, ws As Worksheet, rng, arr(), key
Set dic = CreateObject("Scripting.dictionary")
If Intersect(Target, Columns("C")) Is Nothing Then Exit Sub
    For Each ws In Sheets
        If "North_ShopSouth_ShopWest_Shop" Like "*" & ws.Name & "*" Then
            lr = ws.Cells(Rows.Count, "A").End(xlUp).Row
            rng = ws.Range("A6:C" & lr).Value
            For i = 1 To UBound(rng)
                If Not dic.exists(rng(i, 1)) Then
                    dic.Add rng(i, 1), rng(i, 3)
                Else
                    dic(rng(i, 1)) = dic(rng(i, 1)) + rng(i, 3)
                End If
            Next
        End If
    Next
    With Worksheets("Master_Shop")
        lr = .Cells(Rows.Count, "A").End(xlUp).Row
        rng = .Range("A6:A" & lr).Value
        ReDim arr(1 To lr - 5, 1 To 1)
        For i = 1 To lr - 5
            For Each key In dic.keys
                If key = rng(i, 1) Then arr(i, 1) = dic(key)
            Next
        Next
        .Range("D6").Resize(lr - 5, 1).Value = arr
    End With
End Sub

thank you very very much Mr. Bebo, i've try it. it works,,,,i'm really appreciate your help. As a super noob in vba i'm still learning and understanding your code. but, would you like to show me, what's wrong with my code mr. bebo?

 

[bebo021999]

First, your code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
wish to trigger any change in selection.
Normally, there must be a line of code to help VBA to identify "what change":

If not intersect(target, columns("C")) is nothing then...

to trigger whenever column C is selected.

Second, your code is placed in Master_shop, while any change in column C of slave sheets must be fired.
therefore, each slave sheet must has it's worksheet_change event separately.

 

[Zachary14]

First, your code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
wish to trigger any change in selection.
Normally, there must be a line of code to help VBA to identify "what change":

If not intersect(target, columns("C")) is nothing then...

to trigger whenever column C is selected.

Second, your code is placed in Master_shop, while any change in column C of slave sheets must be fired.
therefore, each slave sheet must has it's worksheet_change event separately.

okey, thank you mr. Bebo. Understood