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