Sum row cell values from multiple workbooks with multiple worksheets and paste it columnwise

Question

This is part 2 of the question here: Sum cell values from multiple workbooks with multiple worksheets - Macro

To summarize my requirement: I have 50 workbooks. Each has 3 worksheets (total of 6. other 3 are irrelevant). Only the first row has values (say 10 values from cells A1 to N1 in sheet1,2and3). I want to sum the first row values from each of the worksheets separately from each workbook and paste it in the macro workbook in sheet 1 , in a single column. (So i will get a final column with 30 values).

I tried continuing the code from the previous question. But I wasn't able to do so. Only modification is pasting it columnwise in single sheet (column D). Any help would be grateful.

What is your code? What is it doing that isn't what you expect? — Raystafarian
it's not clear what output you want, can you show any example? — Dmitry Pavliv
Output - will have 30 values in a single column. First 10 will be the sum of (individual) values row1 of sheet1 from 50 workbooks. Next 10 will be sum of row1 of sheet2 from 50 workbooks. next 10 will have row 1 of sheet3 of 50 workbooks. — user3477709

Dmitry Pavliv Dmitry Pavliv · Accepted Answer · 2014-04-02T17:27:04

Try this one:

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

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

    If Not IsArray(FileNameXls) Then Exit Sub

    Application.ScreenUpdating = False
    'clear previous values
    ThisWorkbook.Sheets("Sheet1").Range("A1:A30").Clear
    For Each f In FileNameXls
        Set wb = Workbooks.Open(f)
        For i = 1 To 3
            wb.Worksheets(i).Range("A1:N1").Copy
            'change Sheet1 to suit
            ThisWorkbook.Sheets("Sheet1").Range("A" & 1 + 10 * (i - 1)).PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, Transpose:=True
        Next i
        wb.Close SaveChanges:=False
    Next f

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

Sum row cell values from multiple workbooks with multiple worksheets and paste it columnwise

1 Answers