Refresh all but 1st sheet

[pgarcia]

Should't this work? Also, it ask to inmport text, how to say "yes" evertime?
Text name will be different ever time.

Sub Refreshall ()
For i = 2 To Sheets.Count
With Active.Sheet
Selection.QueryTable.Refresh BackgroundQuery:=False
Next i
End Sub

Thanks

 

[Andy Pope]

Hi,

Although the loop index goes from to to the number of sheets the refresh is
made on the activesheets selection, which never changes.
Disabling DisplayAlerts should suppress the dialogs.

Try something like this.

Sub Refreshall ()
application.displayalerts=false
For i = 2 To workSheets.Count
worksheets(i).QueryTable.Refresh BackgroundQuery:=False
Next i
application.displayalerts=true
End Sub

Cheers
Andy

 

[Shane Devenshire]

Hi,

First, you should get an error with the With Active.Sheet command for 2
reasons
1. the command is ActiveSheet
2. every With should have and End With

You might modify you code to read:
Sub Refreshall ()
For i = 2 To Sheets.Count
Sheets(i).Range("A1").QueryTable.Refresh BackgroundQuery:=False
Next i
End Sub

As I read it the QueryTable.Refresh command requires a range that intersects
the querytable, so you could use selection if you can be sure the cursor is
in the querytable range. In the example above range A1 is assumed to be in
the querytable and below the range named Data is also.

I have not tested this code - you might need to make it read:

Sub Refreshall ()
For i = 2 To Sheets.Count
Sheets(i).Activate
Range("Data").QueryTable.Refresh BackgroundQuery:=False
Next i
End Sub

Cheers,
Shane Devenshire