Power query refresh of query table on hidden sheet crashes excel

[ITTom365]

Hi, 

I have been working on replacing VBA (with PowerQuery) when gathering a large data set and using the resulting table to create pivot tables and ultimately a visual (pivot charts) dashboard.

After a couple of questions on this forum I have things working well (albeit PQ seems slower than my VBA approach), I went to tidy up the excel book; that is to say hidding all sheets except the dashboard itself, however I have come accross two problems I have spent the day on :(

1. If i hide the sheet containing my query table, excel crashes after every refresh. 

details: The refresh completes and it seems to be at the pivot tables refreshes afterwards that the crash happens. It does not crash if the query table sheet is visible

2. If I leave the query table sheet visible, it activates after the refresh and activating the dashboard sheets("TDB").Activate simply doesnt work. 

Sub MyProcedure()
    
    'refresh the query table
    Call ActiveWorkbook.RefreshAll
        
        'check the refresh is complete before continuing VBA
        Call NotifyWhenRefreshComplete
    
                'Call VBA to fill some table columns with formulas
                Call Tbls.PopTable
                
                        'refresh the pivots
                            Call Pivots.RefreshPivots

                
    'Activate the Dashboard
    ThisWorkbook.Sheets("TDB").Activate


End Sub

Private Sub NotifyWhenRefreshComplete()
 
With Worksheets("dataTableSheet").ListObjects.Item(1).QueryTable


    If .Refreshing Then
        Call Application.OnTime(Now + TimeValue("00:00:01"), "NotifyWhenRefreshComplete")
    Else
        Call MsgBox("Refresh Complete.", vbOKOnly)
    End If
    
   End With
End Sub

If I comment out the hack for awaiting the refresh before continuing ('Call NotifyWhenRefreshComplete) and step through the code everything works great! 

Any help greatly appreciated, 

Thanks