来自多个工作簿的单元格值与多个工作表 - 宏 [英] Sum cell values from multiple workbooks with multiple worksheets - Macro

问题描述

• 我有50本工作簿。每个都有3个工作表。


• 我从单元格A1到N1中的每张表中都有一个值。


• 我想总结所有的值在单一的工作簿（分别在3个工作表）。
  （即sheet1工作簿1的A1 + sheet1工作簿2的+ A1 + sheet1的工作簿50的A1 + Sheet1 MacroWorkbook的A1。

同样的Sheet1工作簿1的工作簿1 + B1工作簿2 + ..... +工作簿的工作簿的B1 +工作簿的B1 B1工作簿的工作簿的$ B1
3张和50个工作簿。

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

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

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

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

With the help of different forums, I have tried getting the sum from multiple workbooks from 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

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

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

推荐答案

如果您要求所有工作簿中的值（从每个工作表 A1：N1 ），并粘贴到thisWorkbook的 A1：N1 中，使用以下命令：

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

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

这篇关于来自多个工作簿的单元格值与多个工作表 - 宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！