Copying the content of one cell into another worksheet of the same workbook

I would like to automatically copy the total amount of the "invoices" worksheet into the "sales" column of the "general" worksheet each day.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
not too sure if I understand your question, but think this can be done using vlookup function.



Open in new window

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
For five dates, totals you have on Invoice sheet don't match with the five values for those five dates in Sales column on General Sheet.

Is this correct or you just have shown the example figures in Sales Sheet on General Sheet?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Right click on Invoice Tab --> View Code --> And paste the code given below into the opened code window.

How this code works?

On Invoice Sheet, once you input a value for any date in col. B to col. AS, the sum of values in that row will be automatically be placed in the corresponding cell in col. AT and the sum in col. AT will be transferred to the general Sheet in col. D (Sales) automatically.
So your Invoice sheet contains no formula, you just need to input the figures between col. B to AS and all will be done automatically.
Input the data for next date on Invoice sheet and you will come to know how the code works.

I have also corrected some of your formulas on general sheet to avoid errors.

Here is the used code...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Dim Dws As Worksheet
Dim r As Long
Set Dws = Sheets("general")
If Not Intersect(Target, Range("B:AS")) Is Nothing And Target.Row > 1 And Target <> "" Then
    Range("AT" & Target.Row).Formula = Application.Sum(Range(Cells(Target.Row, "B"), Cells(Target.Row, "AS")))
    Range("AT" & Target.Row).Value = Range("AT" & Target.Row).Value
    Range("AT" & Target.Row).Style = "Currency"
    If Application.CountIf(Dws.Columns(1), Cells(Target.Row, "A")) > 0 Then
        r = Application.Match(Cells(Target.Row, "A"), Dws.Columns(1), 0)
        Dws.Cells(r, "D") = Cells(Target.Row, "AT")
        Dws.Cells(r, "D").Style = "Currency"
    End If
End If
End Sub

Open in new window

for details refer to the attached workbook.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.