Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code
I have a seeming simple task that I can't figure out.
I have a macro set up to refresh a query and then do some copying and pasting of cells. What code can I use to tell the macro to wait until the query is finished refreshing until it does the copy and paste? Here is the simple code. Sub GetData() ThisWorkbook.RefreshAll ' I need the following code to be run after the refresh is finished' Range("J5").Select Selection.Copy Range("J6:J65000").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveWindow.LargeScroll ToRight:=-1 Range("D1").Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code
Preacher Man wrote:
I have a seeming simple task that I can't figure out. I have a macro set up to refresh a query and then do some copying and pasting of cells. What code can I use to tell the macro to wait until the query is finished refreshing until it does the copy and paste? Here is the simple code. Sub GetData() ThisWorkbook.RefreshAll ' I need the following code to be run after the refresh is finished' Range("J5").Select Selection.Copy Range("J6:J65000").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveWindow.LargeScroll ToRight:=-1 Range("D1").Select End Sub Hi, maybe you can split your GetData macro in two macros: Sub GetData() ThisWorkbook.RefreshAll Call CopyAndPaste End Sub Sub CopyAndPaste() Range("J5").Copy Range("J6:J65000") ActiveWindow.LargeScroll ToRight:=-1 Range("D1").Select End Sub -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code
Objects that have the BackgroundQuery property set to True are refreshed in
the background. You need to set them to false. That way the VBA will wait until the refresh completes. -- Gary''s Student "Preacher Man" wrote: I have a seeming simple task that I can't figure out. I have a macro set up to refresh a query and then do some copying and pasting of cells. What code can I use to tell the macro to wait until the query is finished refreshing until it does the copy and paste? Here is the simple code. Sub GetData() ThisWorkbook.RefreshAll ' I need the following code to be run after the refresh is finished' Range("J5").Select Selection.Copy Range("J6:J65000").Select ActiveSheet.Paste Application.CutCopyMode = False ActiveWindow.LargeScroll ToRight:=-1 Range("D1").Select End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Code to conditional format all black after date specified in code? | Excel Discussion (Misc queries) | |||
Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
Code stops when high lighting (Chips code) | Excel Programming | |||
stubborn Excel crash when editing code with code, one solution | Excel Programming |