CODEWITHSUNDEEP
vbaexcelsum
user3477709
user3477709

Reputation: 25

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

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.

Upvotes: 1

Views: 783

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35863

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

Upvotes: 1

Related Questions