VBA Web query - How can I specify destination to named sheet

[suprsnipes]

Hi,

I have a web query created using VBA and would like to know how to change the destination from ActiveSheet to specific sheet name? In this case "bar".

With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;" & MyUrl, Destination:=Cells(1, 1))
    .PostText = MyPost
    .RefreshStyle = xlOverwriteCells
    .PreserveFormatting = True
    .AdjustColumnWidth = False
    .WebFormatting = None
    .BackgroundQuery = True
    .TablesOnlyFromHTML = True
    .Refresh BackgroundQuery:=False
    .SaveData = True
End With

suprsnipes

 

[Norie]

Worksheets("bar") should do it.

 

[suprsnipes]

Thanks Norie ... where does the code go?

Worksheets("bar")

 

[Norie]

Thanks Norie ... where does the code go?

Worksheets("bar")

It replaces ActiveSheet.

There's nothing else I can see in the posted code that need changes.

I could have missed something of course, and if there is more code it could make a difference.

 

[suprsnipes]

This did the trick. Thanks.

Sheets("bar").Select
With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;" & MyUrl, Destination:=Cells(1, 1))
    .PostText = MyPost
    .RefreshStyle = xlOverwriteCells
    .PreserveFormatting = True
    .AdjustColumnWidth = False
    .WebFormatting = None
    .BackgroundQuery = True
    .TablesOnlyFromHTML = True
    .Refresh BackgroundQuery:=False
    .SaveData = True
End With

 

[Norie]

Did you try replacing ActiveSheet with Worksheets("bar")?

 

[suprsnipes]

Yes I did, but received an error. Tried it again just now with no problems so I must have made a mistake. Appreciate your help.

 

[Norie]

I think I know what the problem was, and I did sort of notice it earlier but wasn't sure.

Anyway there's something else that needs to be changed - Cells(1,1).

Without a worksheet reference that will refer to the active worksheet, so if you explicitly tell VBA to place the query table on the worksheet 'bar' and
that isn't active then you'll get an error.

Anyway, replace Cells(1,1) with Worksheets("Bar").Cells(1,1) and it should work no matter what worksheet is active.

 

[suprsnipes]

It's working great after your help. Thanks Norie.