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.
'refresh the query table
'check the refresh is complete before continuing VBA
'Call VBA to fill some table columns with formulas
'refresh the pivots
'Activate the Dashboard
Private Sub NotifyWhenRefreshComplete()
If .Refreshing Then
Call Application.OnTime(Now + TimeValue("00:00:01"), "NotifyWhenRefreshComplete")
Call MsgBox("Refresh Complete.", vbOKOnly)
If I comment out the hack for awaiting the refresh before continuing ('Call NotifyWhenRefreshComplete) and step through the code everything works great!