I'm having trouble with using workbook events to fire a simple macro that deletes a couple of worksheets and refreshes a pivot table.

The scenario is that an accounting application I'm using spools reports into excel, specifically onto a template that i've setup to include a pivot table with a dynamic named range. THe application outputs rows of data onto one sheet i'm then using the workbook close event to trigger a macro which (should!) refresh the pivot table and delete some unneeded sheets. I know the macro fires because the unneeded sheets are deleted but the pivot table doesn't refresh.
Using exactly the same code outside of the scenario of the accounting application it works perfectly.

A) What's going on?

B) More importantly what can I do about it? is there another way to refresh the pivot table other than the code i'm using that might work?
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
'this checks if the data has spooled in from the accounting application
If Not Sheets(1).Range("A8").Value = "" Then

    If WorksheetExists("FLAG") Then
        
        'this should refresh the pivot table         
        Dim pvtTable As PivotTable
        Worksheets("Pivot").Select
        Set pvtTable = Worksheets("Pivot").Range("B5").PivotTable
        pvtTable.RefreshTable

        Application.DisplayAlerts = False
        Worksheets("FLAG").Delete
        Worksheets("Data").Delete
        Worksheets("Options").Delete
        ActiveWorkbook.Save
        Application.DisplayAlerts = True
    End If
    
End If

On Error GoTo 0

End Sub

Public Function WorksheetExists(ByVal WorksheetName As String) As Boolean

On Error Resume Next
WorksheetExists = (Sheets(WorksheetName).Name <> "")
On Error GoTo 0

End Function
As I say the code does work, its just not refreshing the pivot table specifically when the accounting application works with it - I can take the output report, open and close it and the pivot table will refresh, though as I say the macro is firing because the worksheets get deleted.