Private Sub UpdateAllData_WaitPQ()
Dim wb As Workbook
Dim qt As QueryTable
Dim ws As Worksheet
Dim lo As ListObject
Dim pt As PivotTable
Dim refreshing As Boolean
Dim calcMode As XlCalculation
Dim toolong As Boolean
Dim t As Date

    Set wb = ThisWorkbook
    Application.ScreenUpdating = False
    calcMode = Application.Calculation
    Application.Calculation = xlCalculationManual
    Application.DisplayAlerts = False
    t = Now
    
    On Error GoTo fin
    
    wb.RefreshAll
    
    Do
        If Now - t > TimeSerial(0, 0, 30) Then
            If MsgBox("Для обновления необходимо еще какое-то время. Продолжить?", vbQuestion + vbYesNo) = vbNo Then
                toolong = True
            Else
                t = Now
            End If
        End If
        
        refreshing = False
        
        For Each ws In wb.Worksheets
            For Each lo In ws.ListObjects
                Set qt = getLoQueryTable(lo)
                If Not qt Is Nothing Then
                    refreshing = qt.refreshing
                    Debug.Print qt.Connection & " " & refreshing
                    If refreshing Then Exit For
                End If
                DoEvents
            Next lo
            If refreshing Then Exit For
        Next ws
        
    Loop Until Not refreshing Or toolong
    
    If refreshing Then Err.Raise vbObjectError + 513, , "Обновление не завершено!"
    If Not wb.Model Is Nothing Then
        wb.Model.Refresh
    End If
    
    For Each ws In wb.Worksheets
        For Each pt In ws.PivotTables
            pt.PivotCache.Refresh
            pt.RefreshTable
        Next pt
    Next ws
    
    MsgBox "Обновление завершено!", vbInformation
    
fin:
    Application.Calculation = calcMode
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
    If Err.Number <> 0 Then MsgBox "Внимание: " & Err.Description, vbCritical
    
End Sub

Private Function getLoQueryTable(lo As ListObject) As QueryTable
On Error Resume Next
    Set getLoQueryTable = lo.QueryTable
On Error GoTo 0
End Function

  
...
If Not qt Is Nothing Then
    DoEvents
    refreshing = qt.refreshing
    Debug.Print qt.Connection & " " & refreshing
End If
...