Sum the total of a column in excel and paste the sum to a different workbook

Question

I have a workbook with a macro, and I'm going to use the macro to open a different workbook called "filename." Once this second workbook has been opened, I'm going to sum column AJ, and then I'm going to use that value to copy and paste the total value into cell C29 on the first workbook. This will all be done using Excel and VBA.

Sub vba_open_workbook()

Application.Calculation = xlCalculationAutomatic

filename = Range("G11")

Workbooks.Open filename
Workbooks(1).Activate

Range("C29") = Application.WorksheetFunction.Sum(Range("AJ:AJ"))

End Sub

Answer 1

Get Column Sum From Closed Workbook

Option Explicit

Sub AcquireSum()

    'Application.Calculation = xlCalculationAutomatic ' ?
    
    Dim dwb As Workbook: Set dwb = ThisWorkbook ' workbook containing this code
    Dim dws As Worksheet: Set dws = dwb.Sheets("Sheet1") ' adjust!
    Dim dCell As Range: Set dCell = dws.Range("C29")
    
    Dim sFilePath As String: sFilePath = dws.Range("G11").Value
    
    Dim swb As Workbook: Set swb = Workbooks.Open(sFilePath)
    Dim sws As Worksheet: Set sws = swb.Sheets("Sheet1") ' adjust!
    Dim sSum As Variant: sSum = Application.Sum(sws.Columns("AJ"))
    swb.Close SaveChanges:=False ' it was just read from
    
    If IsError(sSum) Then
        MsgBox "Errors in column. Sum not acquired.", vbCritical
        Exit Sub
    End If
    
    dCell.Value = sSum
    
    MsgBox "Sum acquired.", vbInformation

End Sub