Worksheet tabs to include date from a cell

A Worksheet Change: Rename Sheet

The Worksheet Change event is triggered when you 'manually' change a cell. This includes the following: 
 - After entering edit mode, i.e., using F2 or double-clicking a cell to modify it, but only after confirmation (pressing Enter). This doesn't necessarily mean the value was modified. Also, if you exit edit mode with the ESC key, the event is not triggered.
 - After cut/copy-pasting.
 - After writing to cells using VBA.
In your particular case, the event will only trigger when you change the value in Sheet1, since Sheet3 has a formula that will not trigger the event.
Therefore, you need to use the code only for Sheet1 and update both sheet names when this change happens.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim targetCell As Range: Set targetCell = Me.Range("H1")
    If Intersect(targetCell, Target) Is Nothing Then Exit Sub
    
    Dim sDate As String: sDate = Format(targetCell.Value, "mmm-dd-yyyy")
    
    Me.Name = "AR - as of " & sDate
    Sheet3.Name = "AP - as of " & sDate

End Sub