VBA Code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Preacher said:
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
Click to expand...



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
 
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.
 
Back
Top