Populate amounts for separated range based on matching headers across sheets for partial

Hello
in summary sheet there is multiple ranges contains headers are red font color should match these partial items with others sheets are existed in column B and should sum amount the for the whole column whether in the same sheet or across sheet for DEBIT or CREDIT columns after fill ranges then should subrtact separated range from adjacent separated range in column I:L
for instance :
NET PUR PURCHASE
will subtract 178800-6540=172260 as in I3
so should take next two separated ranges and subtract from each other .
notice: there partial item is repeated for more than one sheet ,then should merge across sheets.
the result in last sheet but suppose in summary sheet.

ok see the rows 19,20 in first sheet, and F22 in summary sheet

So weird to overcomplicate with such bad Summary worksheet layout and
to not use directly worksheet formulas rather than VBA …

Anyway according to your attachment an Excel basics VBA demonstration for starters :

PHP Code: 

Sub Demo1()
    Dim R&, C%, T(1, 0), S&
        R = 2
    With Worksheets("summary")
    While Not IsEmpty(.Cells(R, 3))
    For C = 3 To 6 Step 3
        T(1, 0) = 0
    For S = 1 To .Index - 1
        T(1, 0) = T(1, 0) + Worksheets(S).Evaluate(Replace("SUMIF(B:B,""#*"",C:C)-SUMIF(B:B,""#*"",D:D)", "#", .Cells(R, C)))
    Next
        T(0, 0) = Array("DEBIT", "CREDIT")(-(T(1, 0) <= 0))
        T(1, 0) = Abs(T(1, 0))
       .Cells(R + 1, C).Resize(2) = T
    Next
        R = R + 6
    Wend
    End With
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

Originally Posted by abdo M

I no know what's my bad !

Your bad is your summary cell F21 equal to 'DEBIT' as first sheet rows #19 & 20 values are in 'CREDIT' column …

@nankw83
now after Marc make attention for my bad your code works perfectly .
thank you so much .

Your bad is your summary cell F21 equal to 'DEBIT' as first sheet rows #19 & 20 values are in 'CREDIT' column …

thanks Marc ,
by the way you forgot showing in range H:last column (last column means add new item based on separated range) .

Thanks for the rep' !

I not forgot as it's useless just using formulas and
my demonstration shows how to get rid of summary existing DEBIT / CREDIT …

I not forgot as it's useless just using formulas and

if the formula depends on header (H2:.....) then it's useless because I will add new header for new formula for every time add new header.

No header as always just with cell reference like for your useless TOTAL cells …

you're talking about sumif formula? , if it's so forgive me this is the only way I know it.

No as I were stating about H3:L3 …
As any Excel user does not need any VBA code but just formulas for the worksheet summary !

But for lazy just with an UDF like :

PHP Code: 

Function GetSum@(ByVal K$)
     For S& = 1 To Application.ThisCell.Parent.Index - 1
         GetSum = GetSum + Worksheets(S).Evaluate(Replace("SUMIF(B:B,""#*"",D:D)-SUMIF(B:B,""#*"",C:C)", "#", K))
     Next
End Function 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

just question I have to change structure as you did it ?
it's not possible to do that my way ?

No, just an example according to Excel rules as when using formulas it is not necessary to launch manually any VBA procedure
when any source data changes like with your over complicated way …

Hi Marc ,
I'm still interesting for your code , but I think you don't understand totally my request in H:L
my way should subtract directly without show target columns for subtraction as your way and if I use function , then I have to select individual range manually . so why in this case vba is useless?
my way in subtraction should be between C,F for each two consecutive range

Ok, go with post #2 …