Excel VBA Use Selected Sheet

Question

Excel VBA is new here. I just need a macro that will update the queries that I have on the single sheet that I'm viewing. I already have an update macro, but I always need to specify the name of the sheet that I want to update. Is it possible to run a macro on any sheet that I am viewing? Here's the macro in it current state:

Sub Refresh_Query() Sheets("Sheet1").Select Range("B6").Select Selection.QueryTable.Refresh BackgroundQuery:=False End Sub 

Answer 1

You want to use ActiveSheet.Name , for example:

 Sub Refresh_Query() Sheets(ActiveSheet.Name).Select Range("B6").Select Selection.QueryTable.Refresh BackgroundQuery:=False End Sub 

Answer 2

This should work:

 Sub Refresh_Query() ActiveSheet.QueryTables(1).Refresh BackgroundQuery:=False End Sub 

Answer 3

The OP parameter is ambiguous: the text asks to update all the query tables on the active sheet, but the sample code updates only one query table containing cell B3

To update only one query table, use

 Sub RefreshOneQuery() Dim qt As QueryTable On Error Resume Next ' in case there is no qt containing cell B6 Set qt = Range("B6").QueryTable On Error GoTo 0 If Not qt Is Nothing Then qt.Refresh BackgroundQuery:=False End If End Sub 

To update all query tables in a worksheet, use

 Sub RefreshAllQueries() Dim qt As QueryTable For Each qt In ActiveSheet.QueryTables qt.Refresh BackgroundQuery:=False Next End Sub