CODEWITHSUNDEEP
excelvba
user9092346
user9092346

Reputation:

Summing Range in one Workbook and post it to another Workbook

I need to sum the Range "C:C" in Workbook_A and paste the result into the Cells "B2" in Workbook_B. All I can find so far a really long and complicated solutions or some that first copy the range to Workbook_B and then do the summing up there. Is there a VBA one-liner to do this (Makro sitting in Workbook_B - the target)?

Copying a single cell from Workbook_A to Workbook_B works just fine:

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

What doesn't work for me is:

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

Also doesn't work

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

Upvotes: 0

Views: 247

Answers (2)

JvdV
JvdV

Reputation: 75840

In addition to @SJR his solution. You need to define your ranges. Example:

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

Upvotes: 1

SJR
SJR

Reputation: 23081

Last one was nearly there. Try

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

You need to fully define the range being summed - i.e. put everything inside the sum function.

Upvotes: 1

Related Questions