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

Question

So I have a workbook with a macro, i will use the macro to open a different workbook called 'filename' once I have opened this 2nd workbook I will sum column AJ then with that value I would like to copy and paste the total value to cell C29 on the first workbook all in 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