SUMIF IN VBA with external references

[vinirato]

Hey, I am new in this forum and i need some help
I've got a lot of excel sheets with the projected seels of each seller.

Now I want to consolidate all the info. I first uses sumif in all the sheets, but the arqchive is too heavy now. That's why i am trying to use a VBA code to do the function sumif.


I tried this one below. However using this code, it opens all the sheets get the data and then close them. It si impossilbe to do like that because there are a lot of sheets that the code will open.
-------------------------------------------

[VBA]
Sub Somase()
Dim dblAnswer As Double

Application.ScreenUpdating = False
Set ws1 = ActiveSheet
Set wb = Workbooks.Open("C:\Users\Vinicius\Desktop\teste.xlsm")
Set ws2 = wb.Sheets("Plan1")

ws1.Range("b1") = Application.WorksheetFunction.SumIf(ws2.Range("a1:a10"), _
ws1.Range("a1"), ws2.Range("b1:b10"))
ws1.Range("b2") = Application.WorksheetFunction.SumIf(ws2.Range("a1:a10"), _
ws1.Range("a2"), ws2.Range("b1:b10"))


wb.Close False
Application.ScreenUpdating = True

End Sub
[/VBA]------------------------------------------------------------------------

Is that a way to use a VBA code that i dont need to open all the sheets to atualize the data e to get a sheet less heavier?


Thank you very much. And sory about about my english mistakes.

[austenr]

Have a look at this:

http://www.exceltip.com/st/Summing_A...eets/1066.html

[vinirato]

i know how to use the sumif formula in excel across multiple worksheets.

I want to do that in VBA code becuase the worksheet is too heavy and I have to open all the worksheets where the data come from to uptade the data.

[vinirato]

anyone can help me????

[mdmackillop]

Can you post a sample sheet with a small amount of typical data and actual layout

[Bob Phillips]

ARe you talking about multiple workbooks or multiple worksheets, your explanation is confusing.

Eithre way, you need to better explain what the requirement is.

[vinirato]

I am talking about multiple worksheets.

Im gonna try to explain better.

I have 15 worksheets. In each worksheet there are the projected salles (Value and quantity) of 700 products for the whole year.

I create another worksheet that is a sum of all 15 worksheets. However this consolidated worksheet is a problem. First, because to uptade my data i need to open al the 15 worksheets and second because with al the sumif I did the consolidated worksheet is too heavy.

So that why i am asking you guys if there is a way i could use a vba code to make the worksheet faster and less heavy. And if is possible a way to uptade the data without having to open all 15 files.

Thanks.

[Bob Phillips]

Why do you say that you have to open all 15 worksheets? I don't understand what open a worksheet means, you certainly do NOT have to activatre them all to reference them in formulae. And what does too heavy mean?

[Reactor77]

To allow the remote references to be updated without opening the worksheet that contains the references, you can use a combination of Sum() and If()
in an array formula.
={SUM(IF(A1:A20=C1,B1:B20)}

[vinirato]

Why do you say that you have to open all 15 worksheets? I don't understand what open a worksheet means, you certainly do NOT have to activatre them all to reference them in formulae. And what does too heavy mean?

Xld, what i want to do is create a worksheet that is a sum of all the 15 files. Is there a way to do that using a vba code? Using a vba code, once I make a change in one of the 15 files, do I need to open it to updated the sum in my consolidated worksheet?

[vinirato]

To allow the remote references to be updated without opening the worksheet that contains the references, you can use a combination of Sum() and If()
in an array formula.
={SUM(IF(A1:A20=C1,B1:B20)}

Reactor..how do i do that if the range and the sum range is in another file?

[Reactor77]

I do this in my work with 20 files that have needed ranges. But of course it was routine when i do references manually. I’m not a VBA guru yet

And using SUMIF I have to open all 20 files, but when I reopen master file and make some changes, my data has lost. So I find another way to solve my task, using SUM and IF separately.

[Bob Phillips]

Now you are saying files, previously you said worksheets. They are very different, so at this point I have no idea what you want to do.

If they are actual files and you use SUMPRODUCT, they don't all have to be open. But if you have 15 workbooks, I would suggest a database.