vba 将多个工作簿中的单元格值与多个工作表相加 - 宏

Sum cell values from multiple workbooks with multiple worksheets - Macro

提问by user3477709

• I have 50 workbooks. Each has 3 worksheets.
• I have a value in each of these sheets from cells A1 to N1.
• I want to sum up all the values in a single workbook (in 3 worksheets respectively). (i.e. A1 of sheet1 workbook1 + A1 of sheet1 workbook2 +.....+ A1 of sheet1 workbook50 = A1 of sheet1 MacroWorkbook.

• 我有50本练习册。每个有 3 个工作表。
• 我在从单元格 A1 到 N1 的每个工作表中都有一个值。
• 我想总结一个工作簿中的所有值（分别在 3 个工作表中）。（即 sheet1 workbook1 的 A1 + sheet1 workbook2 的 A1 +.....+ sheet1 workbook50 的 A1 = sheet1 MacroWorkbook 的 A1。

Similarly B1 of sheet1 workbook1 + B1 of sheet1 workbook2 +.....+ B1 of sheet1 workbook50 = B1 of sheet1 MacroWorkbook. For 3 sheets and say 50 workbooks.

类似地，sheet1 workbook1 的 B1 + sheet1 workbook2 的 B1 +.....+ sheet1 workbook50 的 B1 = sheet1 MacroWorkbook 的 B1。对于 3 张纸，并说 50 个工作簿。

I would prefer open file location instead of selecting from a directory.

我更喜欢打开文件位置而不是从目录中选择。

---

---

With the help of different forums, I have tried getting the sum from multiple workbooks from sheet1:

在不同论坛的帮助下，我尝试从 sheet1 的多个工作簿中获取总和：

Sub SUM_Workbooks()
    Dim FileNameXls As Variant
    Dim i As Integer
    Dim wb As Workbook
    FileNameXls = Application.GetOpenFilename(filefilter:="Excel Files, *.xl*", MultiSelect:=True)
    If Not IsArray(FileNameXls) Then Exit Sub
    Application.ScreenUpdating = False
    For i = LBound(FileNameXls) To UBound(FileNameXls)
        Set wb = Workbooks.Open(FileNameXls(i))
        wb.Sheets(1).Range("A1:N1").Copy
        ThisWorkbook.Sheets(1).Range("A1:N1").PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks:=True, Transpose:=False
        Application.CutCopyMode = False
        wb.Close SaveChanges:=False
        Next i
    Application.ScreenUpdating = True
End Sub

I would want to extend this for 3 sheets. Help would be much appreciated as I'm no expert in VBA. Thanks!

我想将此扩展为 3 张。帮助将不胜感激，因为我不是 VBA 专家。谢谢！

采纳答案by Dmitry Pavliv

If you want to sum values from all workbooks (from each worksheet A1:N1) and paste result in A1:N1of thisWorkbook, use this one:

如果您想对所有工作簿（来自每个工作表A1:N1）的值求和并将结果粘贴A1:N1到此工作簿中，请使用以下一个：

Sub SUM_Workbooks()
    Dim FileNameXls, f
    Dim wb As Workbook, i As Integer

    FileNameXls = Application.GetOpenFilename(filefilter:="Excel Files, *.xl*", MultiSelect:=True)

    If Not IsArray(FileNameXls) Then Exit Sub

    Application.ScreenUpdating = False

    For Each f In FileNameXls
        Set wb = Workbooks.Open(f)
        For i = 1 To 3
            wb.Worksheets(i).Range("A1:N1").Copy
            ThisWorkbook.Sheets(i).Range("A1:N1").PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks:=True, Transpose:=False
        Next i
        wb.Close SaveChanges:=False
    Next f

    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub