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
Workbooks(1).Activate

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

End Sub
5 days ago in Others by Kithuzzz
• 27,740 points
35 views

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 5 days ago by narikkadan
• 51,240 points

Related Questions In Others

0 votes
1 answer

Is there any way in python to auto-correct spelling mistake in multiple rows of an excel files of a single column?

Use Spellchecker for doing your stuff: import pandas ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 51,240 points
285 views
0 votes
1 answer

How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 51,240 points
138 views
0 votes
1 answer

Creating a function in excel VBA to calculate the average point in a circular set of numbers

I used the following code to determine ...READ MORE

answered Oct 28, 2022 in Others by narikkadan
• 51,240 points
142 views
0 votes
1 answer

Calculate the number of days between a cell and today in excel?

Use the DATEDIF function when you want ...READ MORE

answered Nov 8, 2022 in Others by gaurav
• 22,940 points
67 views
0 votes
1 answer

Retrieve epay.info 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
522 views
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
2,382 views
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
• 22,940 points
110 views
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,640 points
274 views
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 in Others by narikkadan
• 51,240 points
35 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP