Reputation: 25
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.
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
I would want to extend this for 3 sheets. Help would be much appreciated as I'm no expert in VBA. Thanks!
Upvotes: 1
Views: 12362
Reputation: 35863
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
Upvotes: 1