Macro to copy same data from multiple workbooks and paste into separate worksheet in one master workbook

[sloh4]

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?

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

 

[RayFrye]

Hi, This might get you started:

Sub Summary(WorkBookName)   'your subroutine
    '...
    
    Set extwbk = Workbooks.Open(WorkBookName)
    
    '...
End Sub

Sub WalkWorkbooks()

    Dim wb As Workbook
    
    For Each wb In Application.Workbooks
      
        Call Summary(wb.Name)   'call your subroutine with the Workbook Name
      
    Next wb

End Sub