CODEWITHSUNDEEP
excelvba
user1985112
user1985112

Reputation: 75

Excel VBA Use selected sheet

Excel VBA newbie here. I just need a macro that will refresh the queries I have on a single sheet I'm viewing. I already have the refresh macro but I always have to specify the sheet name that I want to refresh. Is it possible to have the macro run on whatever sheet I'm viewing? Here's the macro in it's current state:

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

Upvotes: 7

Views: 51000

Answers (3)

chris neilsen
chris neilsen

Reputation: 53135

The OP is ambiguous: text asks to refresh all query tables on the active sheet, but sample code only refreshes the one query table that contains cell B3

To refresh 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 refresh all query tables on the sheet use

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

Upvotes: 1

chuff
chuff

Reputation: 5866

This should work:

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

Upvotes: 2

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32680

You want to use ActiveSheet.Name, such as:

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

Upvotes: 9

Related Questions