Running sum that does not delete

[Woodwork]

Good morning all,
This is my first post here and I am far from being an Excel guru so please excuse my ignorance.
With that said, the problem I'm having is:
I have a 2 sheet workbook, SHEET1 is daily sales and SHEET2 is year to date (YTD). Sheet 1 carries over to sheet 2. At the end of every day the entries in sheet 1 get deleted which also removes them from sheet 2. Is there a VBA I can use that will allow me to delete Sheet 1 entries but have them remain in sheet2 (YTD) creating a running total for the year?

Thank you

 

[Joe4]

Welcome to the Board!

At the end of every day the entries in sheet 1 get deleted which also removes them from sheet 2

How exactly does that happen?

Are the entries on Sheet2 formulas/links to the values on Sheet1?
If they are, simply use Copy -> Paste Special -> Values to overwrite all the formulas on Sheet2 with their hard-codes values. Do this before deleting the data on Sheet1.

If they are not formulas on Sheet2, then something (or someone) is physically, consciously deleting the data on Sheet2. In that case, just stop/remove that process that deletes the data from Sheet2.

 

[Woodwork]

Thank you Joe4,
Sheet 2 is linked to sheet 1 by formulas. When entries are made into sheet 1 they automatically go into the corresponding cell in sheet2. I was hoping that there was a way that the numbers would just stick in sheet 2 without having to copy and paste every day

 

[Woodwork]

This is the code that I am using so the cells will add themselves:


Private Sub Worksheet_Change(ByVal Target As Range)
'Apply to columns B:T.
If Target.Column = 1 Or Target.Column > 20 Then Exit Sub
'Only one cell at a time can be changed.
If Target.Cells.Count > 1 Then Exit Sub
'OK to delete the cell contents.
If IsEmpty(Target) Then Exit Sub

'Mandate numeric entry.
If IsNumeric(Target.Value) = False Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
MsgBox "You entered a non-numeric value.", 48, "Numbers only in B:T."
Exit Sub
End If

 

[Joe4]

That doesn't tell me anything about the relationship between Sheet1 and Sheet2.
All that code does is force numeric entries. It doesn't add anything, nor does it copy anything to other sheets.

 

[Woodwork]

This is an example of the formula Im using for sheet 2... Sheet 1 is named quantity


=SUM(Quantity!D11)

 

[Joe4]

=SUM(Quantity!D11)

Using the SUM function on a single cell doesn't really serve s purpose. You use SUM to sum up multiple values or ranges.
So that formula can just be simplified to:
=Quantity!D11

So it looks like that sheet if referring to the other sheet via formulas. If you want to "keep" those values when the data on the Quantity sheet is deleted, then simply paste over them a Copy -> Paste Special -> Values. You can do all cells at once. You can even create a Macro to do it for you, like this:

Sub MyCopyMacro()
    Cells.Copy
    Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

 

[Woodwork]

Thank you for the help Joe4,
I'll give this a shot

 

[Joe4]

You are welcome.