Hi, I'm a beginner in macro and I managed to write the below codes to pick up certain data from one workbook without opening. However, now, I need to copy the same data from 20 workbooks and paste into separate worksheet in one master workbook. I do not know how to incorporate loop code to make it works. Can someone helps?
VBA Code:
Sub Summary()
Dim LastRow As Long, x As Range
Dim extwbk As Workbook, twb As Workbook
Dim criteria1 As String
Dim criteria2 As String
Dim criteria3 As String
Dim criteria4 As String
Set twb = ThisWorkbook
Set extwbk = Workbooks.Open("D:\Users\sloh4\Documents\BS\BankABCBS.xls")
Set w = extwbk.Worksheets(1).Range("V:V")
Set x = extwbk.Worksheets(1).Range("X:X")
Set v = extwbk.Worksheets(1).Range("T:T")
Set y = extwbk.Worksheets(1).Range("AV:AV")
Set Z = extwbk.Worksheets(1).Range("BB:BB")
Set S = extwbk.Worksheets(1).Range("BR:BR")
With twb.Sheets("Sheet1")
criteria1 = "<>*COMPANY 1*"
criteria2 = "<>*COMPANY 2*"
criteria3 = "<>*COMPANY 3*"
criteria4 = "<>*BankABC*"
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
.Cells(6, 4) = Application.CountIfs(v, "<>" & "", w, (.Cells(6, 3)), Z, criteria1, Z, criteria2, Z, criteria3)
.Cells(6, 5) = Application.SumIfs(v, w, (.Cells(6, 3)), Z, criteria1, Z, criteria2, Z, criteria3)
.Cells(7, 4) = Application.CountIfs(v, "<>" & "", w, (.Cells(7, 3)), Z, criteria1, Z, criteria2, Z, criteria3)
.Cells(7, 5) = Application.SumIfs(v, w, (.Cells(7, 3)), Z, criteria1, Z, criteria2, Z, criteria3)
.Cells(8, 4) = Application.CountIfs(v, "<>" & "", w, (.Cells(8, 3)), x, "<>*returning bank*")
.Cells(8, 5) = Application.SumIfs(v, w, (.Cells(8, 3)), x, "<>*returning bank*")
.Cells(9, 4) = Application.CountIfs(v, "<>" & "", w, (.Cells(9, 3)), x, "*returning bank*")
.Cells(9, 5) = Application.SumIfs(v, w, (.Cells(9, 3)), x, "*returning bank*")
.Cells(10, 4) = Application.CountIfs(v, "<>" & "", w, (.Cells(10, 3)))
.Cells(10, 5) = TRUEROUND(Application.SumIf(w, (.Cells(10, 3)), v), 2)
.Cells(11, 4) = Application.CountIfs(v, "<>" & "", w, (.Cells(11, 3))) + Application.CountIfs(v, "<>" & "", w, "*Same Day Credit*")
.Cells(11, 5) = TRUEROUND(Application.SumIf(w, (.Cells(11, 3)), v), 2) + TRUEROUND(Application.SumIf(w, "*Same Day Credit*", v), 2)
.Cells(12, 4) = Application.CountIfs(v, "<>" & "", w, (.Cells(12, 3)))
.Cells(12, 5) = TRUEROUND(Application.SumIf(w, (.Cells(12, 3)), v), 2)
.Cells(13, 4) = Application.CountIfs(v, "<>" & "", w, (.Cells(13, 3)))
.Cells(13, 5) = TRUEROUND(Application.SumIf(w, (.Cells(13, 3)), v), 2)
.Cells(14, 4) = Application.CountIfs(v, "<>" & "", w, (.Cells(14, 3)))
.Cells(14, 5) = TRUEROUND(Application.SumIf(w, (.Cells(14, 3)), v), 2)
.Cells(15, 4) = Application.CountIfs(v, "<>" & "", w, (.Cells(15, 3)), y, criteria4, Z, "*COMPANY 1*") + Application.CountIfs(v, "<>" & "", w, (.Cells(15, 3)), y, criteria4, Z, "*COMPANY 2*") + Application.CountIfs(v, "<>" & "", w, (.Cells(15, 3)), y, criteria3, Z, "*COMPANY 3*")
.Cells(15, 5) = Application.SumIfs(v, w, (.Cells(15, 3)), y, criteria4, Z, "*COMPANY 1*") + Application.SumIfs(v, w, (.Cells(15, 3)), y, criteria4, Z, "*COMPANY 2*") + Application.SumIfs(v, w, (.Cells(15, 3)), y, criteria3, Z, "*COMPANY 3*")
.Cells(16, 4) = Application.CountIfs(v, "<>" & "", w, (.Cells(16, 3)), y, "*COMPANY 3*", Z, "*COMPANY 3*") + Application.CountIfs(v, "<>" & "", w, (.Cells(16, 3)), y, "*BankABC*", Z, "*COMPANY 1*") + Application.CountIfs(v, "<>" & "", w, (.Cells(16, 3)), y, "*BankABC*", Z, "*COMPANY 2*")
.Cells(16, 5) = Application.SumIfs(v, w, (.Cells(16, 3)), y, "*COMPANY 3*", Z, "*COMPANY 3*") + Application.SumIfs(v, w, (.Cells(16, 3)), y, "*BankABC*", Z, "*COMPANY 1*") + Application.SumIfs(v, w, (.Cells(16, 3)), y, "*BankABC*", Z, "*COMPANY 2*")
.Cells(19, 4) = Application.CountIfs(v, "<>" & "", w, (.Cells(19, 3)), S, "")
.Cells(19, 5) = Application.SumIfs(v, w, (.Cells(19, 3)), S, "")
.Cells(20, 4) = Application.CountIfs(v, "<>" & "", w, (.Cells(20, 3)), S, "*FED*") + Application.CountIfs(v, "<>" & "", w, (.Cells(20, 3)), S, "*CHIPS*")
.Cells(20, 5) = Application.SumIfs(v, w, (.Cells(20, 3)), S, "*FED*") + Application.SumIfs(v, w, (.Cells(20, 3)), S, "*CHIPS*")
.Cells(21, 4) = Application.CountIfs(v, "<>" & "", w, (.Cells(21, 3)))
.Cells(21, 5) = TRUEROUND(Application.SumIf(w, (.Cells(21, 3)), v), 2)
.Cells(22, 4) = Application.CountIfs(v, "<>" & "", w, (.Cells(22, 3)))
.Cells(22, 5) = TRUEROUND(Application.SumIf(w, (.Cells(22, 3)), v), 2)
.Cells(23, 4) = Application.CountIfs(v, "<>" & "", w, (.Cells(23, 3)))
.Cells(23, 5) = TRUEROUND(Application.SumIf(w, (.Cells(23, 3)), v), 2)
.Cells(24, 4) = Application.CountIfs(v, "<>" & "", w, (.Cells(24, 3))) + Application.CountIfs(v, "<>" & "", w, "*CHECK OVERRIDE*")
.Cells(24, 5) = TRUEROUND(Application.SumIf(w, (.Cells(24, 3)), v), 2) + TRUEROUND(Application.SumIf(w, "*CHECK OVERRIDE*", v), 2)
.Cells(25, 4) = Application.CountIfs(v, "<>" & "", w, (.Cells(25, 3)))
.Cells(25, 5) = TRUEROUND(Application.SumIf(w, (.Cells(25, 3)), v), 2)
.Cells(26, 4) = Application.CountIfs(v, "<>" & "", w, (.Cells(26, 3)))
.Cells(26, 5) = TRUEROUND(Application.SumIf(w, (.Cells(26, 3)), v), 2)
.Cells(27, 4) = Application.CountIfs(v, "<>" & "", w, (.Cells(27, 3)))
.Cells(27, 5) = TRUEROUND(Application.SumIf(w, (.Cells(27, 3)), v), 2)
.Cells(28, 4) = Application.CountIfs(v, "<>" & "", w, (.Cells(28, 3)))
.Cells(28, 5) = TRUEROUND(Application.SumIf(w, (.Cells(28, 3)), v), 2)
.Cells(29, 4) = Application.CountIfs(v, "<>" & "", w, (.Cells(29, 3)))
.Cells(29, 5) = TRUEROUND(Application.SumIf(w, (.Cells(29, 3)), v), 2)
.Cells(30, 4) = Application.CountIfs(v, "<>" & "", w, (.Cells(30, 3))) + Application.CountIfs(v, "<>" & "", w, "*INTEREST*")
.Cells(30, 5) = TRUEROUND(Application.SumIf(w, (.Cells(30, 3)), v), 2) + TRUEROUND(Application.SumIf(w, "*INTEREST*", v), 2)
End With
twb.Sheets("Sheet1").Cells(38, 5).Value = TRUEROUND(Application.Sum(v), 2)
twb.Sheets("Sheet1").Cells(38, 4).Value = TRUEROUND(Application.Count(v), 0)
extwbk.Close savechanges:=False
End Sub
Last edited by a moderator: