来自多个工作簿的单元格值与多个工作表 - 宏 [英] 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屋!