Excel VBA：仅在公式栏按回车才更新单元格公式问题求助

我使用的公式引用了其他已创建工作表的变量和数据，例如：

='Field Order 1009'!C7

该公式在单元格和公式栏中显示均正确，但只有点击公式栏并按下回车键后，才能计算出正确数据。未按回车时，单元格仅显示公式文本，不会自动引用目标单元格的数值。

我编写了NewTicket宏来批量处理，但尝试用Application.SendKeys "{ENTER}"触发公式计算的方法无效，宏代码如下：

Sub NewTicket()
'Generate a New Field Order (FO)
'Field Orders are to become Field Change Orders - FCO
'Check if any Field Orders ("ticket" from the Sales Order Version of this) have been generated yet
'if no, use cell B1 as starting ticket number
'if yes, find the last empty cell in column A, move up one row, save the value, add one create the new ticket number
'
Dim ws1 As Worksheet
Dim strFileName, workSheetName, newFormula As String
Dim currentTicket, ticket As Integer

Worksheets("FCO Log").Activate
Worksheets("FCO Log").Range("A9").Activate

Set currentTicket = ActiveCell

If ActiveCell.Value = "" Then
    Set currentTicket = Worksheets("FCO Log").Range("L2")
    ActiveCell.Value = currentTicket
    ticket = currentTicket
    
Else
    Do While ActiveCell.Value > 1
        LastTicket = ActiveCell.Value
        ActiveCell.Offset(1).Select
    Loop
    ticket = LastTicket + 1
    ActiveCell.Value = ticket
    ' create the formula text to look up the FO Description in the newly created FO sheet
    'Set FormulaText = "='Field Order '!C7"
    'Application.StatusBar = FormulaText
    ActiveCell.Offset(0, 1).Select
    ' I tried some things here Column B, but then moved to the column K
    ActiveCell.Offset(0, 9).Select
    newFormula = ActiveCell.Value
    MsgBox ActiveCell.Value ' All the values shown look correct
    MsgBox newFormula       ' All the values shown look correct
    ' Copy the contents of this cell
    Selection.Copy
    ActiveCell.Offset(0, -9).Select  ' Return to column B and paste the text formula now merged with the populated ticket number
    ' Paste the contents into this cell
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
   
    'Application.SendKeys "{ENTER}" This did not do anything. If I could get the ENTER in the formula bar...
    
End If

Set ws1 = ThisWorkbook.Worksheets("Template")
ws1.Copy ThisWorkbook.Sheets(Sheets.Count)

workSheetName = "Field Order " & ticket

With ActiveSheet
   .Name = "Field Order " & ticket
   .Range("I5").Value = ticket
End With

Worksheets("FCO Log").Activate

End Sub

内容的提问来源于stack exchange，提问作者Rob Somma