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

0 votes

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

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

End Sub
Jan 26, 2023 in Others by Kithuzzz
• 38,010 points

1 answer to this question.

0 votes

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
answered Jan 26, 2023 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

Excel VBA- How to loop through specific sheets in a workbook and format the same ranges in each sheet

Range(...) instructs VBA to always use the ...READ MORE

answered Mar 21, 2023 in Others by Kithuzzz
• 38,010 points
0 votes
1 answer

How to automatically assign a color to the maximum and minimum values in a set of selected cells in Excel?

See Conditional Formatting, which may be accessed ...READ MORE

answered Apr 7, 2023 in Others by Kithuzzz
• 38,010 points
0 votes
1 answer

Retrieve Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
0 votes
1 answer
0 votes
1 answer

Output python dataframe to excel and create a new data_validation column in the exported excel sheet

Use pandas.ExcelWriter with worksheet.data_validation from xlswriter : df["code"] = None items = list(range(1,10)) max_row, max_col = ...READ MORE

answered Jan 24, 2023 in Others by narikkadan
• 63,720 points
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP