来自具有多个工作表的多个工作簿的和行行单元格值，并将其逐列粘贴 [英] 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

总结我的要求：
我有50个工作簿。每个都有3个工作表（共6个，其他3个是不相关的）。只有第一行有值（例如在sheet1,2和3中，从单元格A1到N1的10个值）。我想从每个工作簿中分别从每个工作表中总和第一行值，并将其粘贴到表1中的宏工作簿中，单列。 （所以我会得到一个30个值的最后一列）。

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).

我尝试继续上一个问题的代码。但是我不能这样做。只有修改才能在单页（列D）中逐列粘贴。任何帮助将不胜感激。

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.

推荐答案

尝试这一个：

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

这篇关于来自具有多个工作表的多个工作簿的和行行单元格值，并将其逐列粘贴的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！