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

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

CodePudding user response：

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

CodePudding user response：

You should always use explicit referencing when using the Range-object.

If not: it refers to the active sheet. That means that your current code inserts ths sum on the same sheet from which it takes the values to be summed.

IMPORTANT: You have to replace the XXXX parts with the worksheet names of your workbooks.

Sub vba_open_workbook()

Application.Calculation = xlCalculationAutomatic

Dim rgSource As Range
Set rgSource = ThisWorkbook.Worksheets("XXXX").Range("AJ:AJ")

Dim Filename As String
Filename = Thisworkbook.Range("G11")

Dim wbTarget As Workbook
Set wbTarget = Workbooks.Open(Filename)

Dim rgTarget As Range
Set rgTarget = wbTarget.Worksheets("XXXX").Range("C29")

rgTarget.Value = Application.WorksheetFunction.Sum(rgSource)

End Sub