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 |
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 |
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 |
All times are GMT +1. The time now is 01:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com