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 :)
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
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
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. :)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.