将一个工作簿中的范围求和范围，然后将其发布到另一个工作簿中

Question

我需要在workbook_a中总结" c：c"范围，然后将结果粘贴到workbook_b中的单元格" b2"中。到目前为止，我只能找到一个非常漫长而复杂的解决方案，或者一些首先将范围复制到Workbook_b，然后在此进行总结。是否有VBA单线可以执行此操作(Makro坐在Workbook_B-目标中(？

将单个单元从Workbook_a复制到Workbook_b工作正常：

Workbooks("Workbook_A.xlsx").Worksheets("Sheet1").Range("C2").Copy  Workbooks("Workbook_B.xlsm").Worksheets("Sheet1").Range("B2")

对我不起作用的是：

Workbooks("Workbook_A.xlsx").Worksheets("Sheet1").WorksheetFunction.Sum(Range("C:C")).Copy Workbooks("Workbook_B.xlsm").Worksheets("Sheet1").Range("B2")

也不起作用

Workbooks("Workbook_B.xlsm").Worksheets("Sheet1").Range("B2").Value = Workbooks("Workbook_A.xlsx").Worksheets("Sheet1").WorksheetFunction.Sum(Range("C:C"))

Answer 1

最后一个几乎在那里。尝试

Workbooks("Workbook_B.xlsm").Worksheets("Sheet1").Range("B2").Value = _
        WorksheetFunction.Sum(Workbooks("Workbook_A.xlsx").Worksheets("Sheet1").Range("C:C"))

您需要充分定义要求和的范围 - 即将所有内容都放在总和函数中。

Answer 2

除了@sjr他的解决方案。您需要定义范围。示例：

Sub Test()
Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Set wb1 = Workbooks("Workbook_A")
Set ws1 = wb1.Sheets("Sheet1")
Set wb2 = Workbooks("Workbook_B")
Set ws2 = wb2.Sheets("Sheet1")
With ws1
    ws2.Range("B2") = WorksheetFunction.Sum(.Range("C:C")) 'Or: .Evaluate("SUM(C:C)")
End With
End Sub