Macro to update all worksheets in workbook
 

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!

Macro to update all worksheets in workbook
 

Hi Christine,

Change:

Range("A1").Select

to:

ws.Range("A1").Select


---
Regards,
Norman


"Christine" wrote in message
om...
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!

Macro to update all worksheets in workbook
 

try this without selection

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

--
Don Guillett
SalesAid Software

"Christine" wrote in message
om...
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!