How to stop Excel from changing the Active Sheet after “Refresh all”?

user_13617992 0
Viewed 194

I have 3 queries (Power Query) in my Excel file that bring information from other different files in table format and in another worksheet I have a button that executes the ThisWorkbook.RefreshAll function.

Sub ButtonRefreshData()
    ThisWorkbook.RefreshAll
End Sub

The problem is: Every time I update my queries, either through the button or Excel's own Refresh/Refresh all tool, my Active Worksheet is changed to the tables that I'm refreshing.

I also tried to set the sheet that I want as active after the refresh with something like ThisWorkbook.Sheets("MySheet").Activate, but no results.

Excel changes my active sheet after refresh all

How can I update my queries without changing my active sheet? I'm currently using Excel 2016.

What I'm trying to do: I want the user to be able to update the data in the worksheet, but I don't want him to have direct access to the tables/data in the worksheets that will be hidden. The user must remain in the "main worksheet", which has the update button.

vba excel
2 Answers

Activate the Previously Active Sheet

  • I couldn't reproduce your issue. I created two queries but both of them didn't change the ActiveSheet. Here are two ideas, the second being a bit too extreme i.e. it may hang or crash Excel. Your feedback is appreciated.

EDIT

Possibly get rid of all related to ash and just use the explicit Sheet1.Select after twRefresh.

Option Explicit

Sub Test1()
    Application.ScreenUpdating = False
    Dim ash As Object: Set ash = ActiveSheet
    twRefresh
    ash.Select
    Application.ScreenUpdating = True
End Sub

Sub twRefresh
    ThisWorkbook.RefreshAll
End Sub

Initial Ideas

Option Explicit

Sub Test1()
    Application.ScreenUpdating = False
    Dim ash As Object: Set ash = ActiveSheet
    ThisWorkbook.RefreshAll
    DoEvents
    ash.Select
    Application.ScreenUpdating = True
End Sub


Sub Test2()
    On Error GoTo ClearError
    
    Application.Visible = False
    Dim ash As Object: Set ash = ActiveSheet
    ThisWorkbook.RefreshAll
    DoEvents
    ash.Select

ProcExit:
    Application.Visible = True
ClearError:
    Debug.Print "Run-time error '" & Err.Number & "': " & Err.Description
    Resume ProcExit
End Sub
user_98140690

Another user who had a similar problem reported that he was unable to resolve the issue and he attributed the cause to the version of Excel he was using, which turned out to be the same as mine.

In this sense, I assume that the cause is some bug in the version: Excel 2016 - 16.0.4266.1001

If you are having the same problem, please try to use newer versions.

user_136179920
Related