excel - 宏从多个工作簿中选择数据并复制到具有不同工作表的一个主工作簿中

Question

我是宏的新手。我编写了一个宏来从一个工作簿中提取某些数据并复制到主工作簿中。我尝试编写宏以从多个工作簿中获取相同类型的数据并复制到不同工作表中的主工作簿中，但失败了。工作簿都位于一个文件夹中。有人能帮我吗？下面是我的宏：

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:\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

标签： excelvba