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

philosopher76 used Ask the Experts™
I would like to automatically copy the total amount of the "invoices" worksheet into the "sales" column of the "general" worksheet each day.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

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 Expert
Most Valuable Expert 2018
Awarded 2015

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?
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial