PDA

View Full Version : SUMIF IN VBA with external references



vinirato
04-05-2010, 11:17 AM
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.
-------------------------------------------


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
------------------------------------------------------------------------

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
04-05-2010, 12:17 PM
Have a look at this:

http://www.exceltip.com/st/Summing_Across_Multiple_Sheets/1066.html

vinirato
04-05-2010, 12:25 PM
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
04-05-2010, 02:43 PM
anyone can help me????

mdmackillop
04-05-2010, 03:09 PM
Can you post a sample sheet with a small amount of typical data and actual layout

xld
04-05-2010, 03:34 PM
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
04-05-2010, 05:04 PM
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.

xld
04-06-2010, 12:35 AM
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
04-06-2010, 04:23 AM
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
04-06-2010, 05:52 AM
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
04-06-2010, 05:54 AM
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
04-06-2010, 06:06 AM
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.

xld
04-06-2010, 06:26 AM
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.