Macro to update all worksheets in workbook

[Christine]

Hi,

I'd like to update ca 100 sheets in a wokbook (all updated via
MSQuery)
by using a macro.

I tried:

Sub Update_all_sheet()

Application.ScreenUpdating = False
Dim ws As Worksheet
For Each ws In Worksheets
Range("A1").Select 'cell in which MSQuery download starts for
each sheet
Selection.QueryTable.Refresh BackgroundQuery:=False
Next ws

End Sub

I also tried:
For each ws in ActiveWorkbook.Sheets

but both only work for the currently active sheet, rather than every
worksheet in workbook.
However, if I use the macro to replace "A" by "B" (as below) this
works fine for each sheet.

Sub ChgInfo()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Cells.Replace What:="A", _
Replacement:="B", LookAt:=xlPart, MatchCase:=False
Next
End Sub

can anyone help me to get the Query updated on all sheets?
Would be great!!!!!
Thanks!

 

[Norman Jones]

Hi Christine,

Change:

Range("A1").Select

to:

ws.Range("A1").Select

 

[Don Guillett]

try this without selection

for each ws in worksheets
ws.QueryTables(1).Refresh BackgroundQuery:=False
next