Macro to update all worksheets in workbook

  • Thread starter Thread starter Christine
  • Start date Start date
C

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!
 
try this without selection

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Placing the MsgBox "No ID found" statement in my code?? 12
Problem in adding macro to ribbon through Add-in 7
Get macro to run on all sheets not just the active sheet 2
Macro to Update all worksheets 3
search and replace loop problem 7
for each sheet in workbook 2
Find dupes, list in MsgBox with cell.address convert ws wide code toone sheet 6
including macro in protected worksheet 2

Back
Top