Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need help to update macro to office 2007 macro enabled workbook | Excel Discussion (Misc queries) | |||
Macro need to update the workbook | Excel Discussion (Misc queries) | |||
How do I set up a workbook that the worksheets update info | Excel Worksheet Functions | |||
Update Links between cells of two worksheets in the same workbook | Excel Discussion (Misc queries) | |||
Can an add-in macro update a worksheets Worksheet_Change function? | Excel Programming |