Merge amounts for each sheet alone with calculation across sheet based on cell value(name)

Hi experts ,
I need to merge amounts for each sheet alone based on cell value (B5) in SL sheet contains name and should match with column C for each sheet alone also based on word(NOT PAID) and ignore PAID in column E should match with B9 in SL sheet and calculation across sheets . when merge amounts should brings amounts from column I for the last row contains TOTAL word in column A .
I take MUSS name as in example in SL sheet inside B5
when sum for SH sheet =880, WS sheet=2503, RS sheet=2490, TR sheet=550
so the final result should be in B7= 880-2503+2490-550=317
I put expected result in last sheet how should show in B7 for SL sheet .
thanks

Code for macro. The macro linked to Button in SL sheet.
PAID and NOT PAID are not there in file.

Please Login or Register  to view this content.

by the way seem the merging is error based on your code .
just use the last row contains TOTAL row until doesn't repeat summing amounts have already sum and show error by sum twice. just take the amount for lastrow as I did it in my example , you will note the error.
like SH sheet for MUSS name and NOT PAID will be = 680+200=880

There is no "PAID" or "NOT PAID" in any column

Look at file #2: you obviously uploaded a new file!

And do not put dark font (black) on dark backgrounds (header rows)!

Please Login or Register  to view this content.

thanks JT
I'm afraid your merging is wrong .
I hope to read this post#4 maybe you understand it and download the file again to find out what you're missed .your result is different what jdelano got it, and me .

Updated version in Post #10 based on KVS code

Originally Posted by Mussa-A

Hi jdelano
seem to this is what I want it , but I need more time to make sure there is no problem.
I will inform you soon.

Okay, sounds good

EDIT: note that on sheet ws the condition was in col D not E, so I switched the invoice and condition columns

@jdelano

EDIT: note that on sheet ws the condition was in col D not E, so I switched the invoice and condition columns

not really understand it !
all of sheets contains PAID,NOT PAID in column E
it's perfect
many thanks buddy

Thank you for the rep.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.