Excel template - evaluate a function only once, upon file creation

Question

Our bookkeeper has an Excel template she uses for creating new purchase orders. She wants to make it so that when she creates a new spreadsheet from this template it will generate a new random number in the purchase order number cell only once.

To minimize the chances of two order numbers being the same I was thinking about using a timestamp as a seed rather than an actual random number, like this: =(NOW()-DATE(1970, 1, 1)) * 864 (I know this won't make a proper Unix timestamp, as that doesn't matter, I just wanted it to be shorter).

My main question is, how can I put a formula into a cell and make sure it evaluates only one time, when a new file is created from this template?

How about having a random number cell and then just pasting the value into the order number cell upon creating the file? — Alexis Olson
@AlexisOlson Well the point of all this is to automate the process so the new PO number is just automatically there when creating a new file from the template. — Chris Hansen
You'd have to copy -> paste special -> values to turn the formula into a value. Otherwise it will keep re-evaluating on every calculation. There's not really a way to prevent a formula from recalculating unless you globally disable workbook calculation (not recommended) — tigeravatar
@tigeravatar Is it possible to put a function into a cell that after it evaluates it replaces itself with its return value so it can't execute again? — Chris Hansen

tigeravatar tigeravatar · Accepted Answer · 2018-02-22T21:46:04

Sample code that you should be able to modify and play around with:

Sub tgr()

    Dim wb As Workbook
    Dim wbNew As Workbook       'Only used if copying template sheet to a brand new workbook
    Dim wsTemplate As Worksheet
    Dim wsNew As Worksheet

    Set wb = ActiveWorkbook
    Set wsTemplate = wb.Sheets("Template")  'Change this to the actual name of your template worksheet

    'If copying the template to a new sheet in same workbook
    wsTemplate.Copy After:=wb.Sheets(wb.Sheets.Count)
    Set wsNew = ActiveSheet

    'If copying the template to a brand new workbook, leave commented out if not using
    'Set wbNew = Workbooks.Add
    'wsTemplate.Copy Before:=wbNew.Sheets(1)
    'Set wsNew = wbNew.ActiveSheet
    'Code here to remove blank sheets from the new workbook if desired

    'wsNew.Name = "Actual Sheet Name"    'Update the name of this newly created sheet
    With wsNew.Range("A1")  'Change to the actual cell that needs to only have its calculation occur once
        .Calculate                      'Update the calculation
        .Value = .Value                 'Convert so that the cell only shows its value and is no longer a formula
    End With

End Sub

Excel template - evaluate a function only once, upon file creation

1 Answers