从不同的工作簿中合并特定的工作表到一个工作表中

I have a single workbook which is edited by 6-7 different people. There are counts given be each person in the sheet and I am looking for a way where I can merge all the sheets and find the sum of the total count. For eg,

我有一本由6-7个不同的人编辑的工作簿。每个人都有计数，我正在寻找一种方法可以合并所有的表并找到总数的总和。例如,

This is the sheet in 1st workbook,

这是第一个工作簿中的表格，

A        B          c
10       15         10

The sheet in the 2nd workbook,

第2工作簿中的表，

A        B          c
7       10          9

And it is similar for all the workbooks. I want the final consolidated one to have the sum of all the values in sheets of each workbook,

所有的练习册都是如此。我想要最终合并的那个包含每个工作簿中所有值的总和，

A        B          c
17       25         19

I thought one way to do this is to, put all the excel workbooks into a same folder and use the following code to merge it and then use a macro to count it.

我认为一种方法是，将所有的excel工作簿放在一个文件夹中，然后使用下面的代码合并它，然后使用一个宏来计数。

Sub GetSheets()
Path = "C:\Users\username\Downloads\New folder"
Filename = Dir(Path & "*.xlsx")
  Do While Filename <> ""
  Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
     For Each Sheet In ActiveWorkbook.Sheets
     Sheet.Copy After:=ThisWorkbook.Sheets(1)
  Next Sheet
     Workbooks(Filename).Close
     Filename = Dir()
  Loop
End Sub

But for some reason this code is getting executed without any errors but has no effect in the consolidated workbook. It is not merging the sheets of the remaining workbooks.

但是由于某些原因，这段代码在执行时没有任何错误，但是在合并工作簿中没有任何影响。它没有合并其余工作簿的表。

Can anybody tell me what is the error I am making here?

有人能告诉我这里的错误是什么吗?

Also is there any alternate way to find the consolidated sum?

还有其他的方法来求合并和吗?

thanks

谢谢

1 个解决方案

#1

Asuming you still want to go with DIR (you need to input checks for name if there are also other files inside the folder)
Tested it at my pc and it worked perfectly:

你仍然想要使用DIR(如果文件夹中还有其他文件，你需要输入名字检查)在我的pc上进行了测试，效果非常好:

Sub SumWB()
  Dim Arr(2) As Long, MyWB As Workbook, fStr as String
  Const Folder = "C:\NewFolder\"
  fStr = Dir(Folder)
  While (file <> "")
    Set MyWB = Workbooks.Open(Folder & fStr, , True)
    Arr(0) = Arr(0) + MyWB.Sheets(1).Range("A1").Value
    Arr(1) = Arr(1) + MyWB.Sheets(1).Range("B1").Value
    Arr(2) = Arr(2) + MyWB.Sheets(1).Range("C1").Value
    MyWB.Close
    file = Dir
  Wend
  Debug.Print Arr(0) & " - " & Arr(1) & " - " & Arr(2)
End Sub