Keeping data in previous worksheets

[jesDav]

I have a workbook at work that we add a new new sheet every day. We just copy the old sheet so that as we update the data in separate spreadsheets it updates in the new sheet (it just copys cells from one sheet to another, not very advanced). Is there a VBA I can put in to keep the previous days data from changing, so we can track every days numbers, since we just reuse the formulas. Or even an easier and more efficient way to do this all?

Thanks for any input!

 

[bobaol]

try doing it manually... 1. copy to new workbook. 2. go back to old workbook. 3. select all in old workbook, copy, paste special, values. you can also use VBA, 4. now everything in old workbook are values. VBA is below, if you want to try it. It was all done using Record Macro. Record Macro is super easy. But in this case, there were manual changes to the code. both versions are included. you can line up both versions side-by-side and see exactly what was changed. Essentially, the change is the sheet name, and using cell E1 as the variable to name the old sheet. hope this helps. - cheers!

Sub Macro1_rewritten_MACRO_EXAMPLE()
'''assume your cell E1 is empty and can be used to hold this formula temporarily
''''this is all done with Record Macro
    
   '''go to e1 in your existing sheet,
    Application.Goto Reference:="R1C5"
    ''enter today's date in text
    Selection.FormulaR1C1 = "=TEXT(NOW(),""yyyymmdd"")"
    Selection.Copy
    
    '''select sheet, rename it to old
    ActiveSheet.Select
    ActiveSheet.Name = "old"
    Sheets("old").Select
    Application.CutCopyMode = False
    
''choose old, copy (duplicate) it
    Sheets("old").Copy Before:=Sheets(1)
    
'''copied sheet is now named  old (2)
    ActiveSheet.Select
    
''re-name it to new
    ActiveSheet.Name = "new"
    
'''go back to old, select A to BZ, copy, paste special, values
    Sheets("old").Select
    Application.Goto Reference:="R1C1"
    ActiveCell.Columns("A:BZ").EntireColumn.Select
    Selection.Copy
    Application.CutCopyMode = False
    Calculate
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.Goto Reference:="R1C1"
    Application.CutCopyMode = False
    Calculate
    
''go back to old, rename the sheet to be cellE1
    Sheets("old").Select
    Sheets("old").Name = Range("e1")
    
''go to new
    Sheets("new").Select
    Application.Goto Reference:="R1C1"
End Sub




Sub Macro2_RECORD_MACRO_EXAMPLE()
'''assume your cell E1 is empty and can be used to hold this formula temporarily
''''this is all done with Record Macro
    
   '''go to e1 in your existing sheet,
    Application.Goto Reference:="R1C5"
    ''enter today's date in text
    Selection.FormulaR1C1 = "=TEXT(NOW(),""yyyymmdd"")"
    Selection.Copy
    
    '''select sheet, rename it to old
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "old"
    Sheets("old").Select
    Application.CutCopyMode = False
    
''choose old, copy (duplicate) it
    Sheets("old").Copy Before:=Sheets(1)
    
'''copied sheet is now named  old (2)
    Sheets("old (2)").Select
    
''re-name it to new
    Sheets("old (2)").Name = "new"
    
'''go back to old, select A to BZ, copy, paste special, values
    Sheets("old").Select
    Application.Goto Reference:="R1C1"
    ActiveCell.Columns("A:BZ").EntireColumn.Select
    Selection.Copy
    Application.CutCopyMode = False
    Calculate
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.Goto Reference:="R1C1"
    Application.CutCopyMode = False
    Calculate
    
''go back to old, rename the sheet to be cellE1
    Sheets("old").Select
    Sheets("old").Name = "cellE1"
    
''go to new
    Sheets("new").Select
    Application.Goto Reference:="R1C1"
End Sub