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.
August-2015.xlsm

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.
August-2015.xlsm

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.

LVL 57

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented: 2015-08-05

not too sure if I understand your question, but think this can be done using vlookup function.

try:

=VLOOKUP(A2,invoices!A:AT,46)

August-2015-b.xlsm

LVL 42

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented: 2015-08-06

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?

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

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

Who is Participating?