PDA

View Full Version : Sum ranges from multiple workbooks VBA



stioks
09-04-2015, 03:56 AM
Hey guys. I am relatively new to VBA, so I have the following issue.

I have three identical workbooks in the same folder with different names, all having the same range filled with numbers. (for example: B2:B25). I want to create a new summary workbook that sums the values from the three files in the same range (b2:b25). In other words, I want cell B2 in the summary workbook to be the sum of cells B2 from the other three workbooks. And so on with b3, b4, b5, etc.

Thanks in advance :)

SamT
09-04-2015, 07:31 AM
Not complete code!
Set SumBk = Summary Book
Set Bk1 = Workbook one
Set bk2 = Workbook two
Set Bk3 = Book 3

For Rw = 2 to ?
SumBk.Range(B" & Rw)).Value = Bk1.Range("B" & Rw).Value + Bk2.Range(B" & Rw)).Value + Bk3.Range(B" & Rw)).Value
Next Rw

stioks
09-04-2015, 09:10 AM
SamT, thanks for the quick reply. But I seem to have problems with figuring out which one should be object here? So i get errors

And another important question:
Can this sum function be implemented to a dynamic number of workbooks (for example all opened workbooks that contain "XXX" in their name?)

This is my code so far


Sub bo()
Dim SumBk As Object

Set SumBk = ThisWorkbook
Bk1 = "C:\_Knowledge\EXCEL TEST\New folder\one.xlsx"
Bk2 = "C:\_Knowledge\EXCEL TEST\New folder\two.xlsx"
Bk3 = "C:\_Knowledge\EXCEL TEST\New folder\three.xlsx"

Thisworksbook.ActiveSheet
For Rw = 2 To 10
SumBk.Range("B" & Rw).Value = Bk1.Range("B" & Rw).Value + Bk2.Range("B" & Rw).Value + Bk3.Range("B" & Rw).Value
Next Rw

End Sub

SamT
09-04-2015, 09:30 AM
In that simple code, you need to Dim all workbook variables as either Object or as Workbook and All such variables must be "Set" = to a workbook.
If the books are not already open, then

Set Bk1 = WorkBooks.Open "C:\_Knowledge\EXCEL TEST\New folder\one.xlsx"

To iterate thru many workbooks in a folder, use the VBA Function "Dir" and SumBk.Range = SumBk.Range + other book.Range.Value

You can get help on any VBA or Excel keyword by placing the cursor inside the Keyword, in the VBA Editor, and pressing F1.

stioks
09-04-2015, 09:54 AM
Now i got "Type mismatch", which marks "C:\_Knowledge\EXCEL TEST\New folder\one.xlsx" in yellow :?


Sub bo()

Dim SumBk As Object
Dim bk1 As Object
Dim bk2 As Object
Dim bk3 As Object




Set SumBk = ThisWorkbook
Set bk1 = "C:\_Knowledge\EXCEL TEST\New folder\one.xlsx"
Set bk2 = "C:\_Knowledge\EXCEL TEST\New folder\two.xlsx"
Set bk3 = "C:\_Knowledge\EXCEL TEST\New folder\three.xlsx"


Thisworksbook.ActiveSheet
For Rw = 2 To 10
SumBk.Range("B" & Rw).Value = bk1.Range("B" & Rw).Value + bk2.Range("B" & Rw).Value + bk3.Range("B" & Rw).Value
Next Rw


End Sub

SamT
09-04-2015, 10:48 AM
Set Bk1 = WorkBooks.Open "C:\_Knowledge\EXCEL TEST\New folder\one.xlsx"

Be sure and close those books at the end of the sub
Bk1.Close, etc.

I wold be more help if I wasn't repairing my car, and only helping while I cool down from the heat. :)