When date changes I lose my data

[Mac]

I have a simple spreadsheet that I want to use daily. Employee enters
numbers daily to make different batches of product. This page does the
calulations for the differents percentages of materials used. I want those
numbers to transfer to a second sheet, third sheet, and so on to keep a daily
record through the year. My problem is I have used the "today function" to
update the day the data is entered by itself. When the day changes the days
entries are deleted and we move to the next day. I am linking the sheets
together with the following: =if(Sheet2!$A9=Sheet1!$f$6,Sheet1!$F$10,0) .
How can I keep my data as the date changes?

 

[Dom_Ciccone]

Could this be as simple as each evening, highlight the sheet you want to
keep, copy it and paste values?

As long as you don't overwrite the template front sheet, this should work.

 

[JLatham]

A one-click solution to do what Dom_Ciccone suggested would be this macro.
It will select the cells on the currently selected worksheet and replace the
formulas with the values of those formulas. i.e., it is a Copy and Paste
Special [Values] function. If you change the name of the worksheet I've
given as "frontSheet" to the sheet main sheet, it will keep it from
accidentally working if you have that sheet selected when you call the macro.

Just use the macro at the end of the day, as Dom suggested.

Sub CarveInStone()
'
'change this worksheet name
'to the name of your 'front' template
'sheet to keep this from affecting it
If ActiveSheet.Name = "frontSheet" Then
Exit Sub
End If

Range("A1").Select
Range(Selection, _
ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Selection.PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
End Sub

 

[Billy Liddel]

Mac

You can use a keyboard short-cut for date and time. Date = Ctrl & ;
while time is Ctrl & Shift & ;

These are entered as numbers and take the system date or system time.

Regards
Peter

=====