VBA Code

[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

 

[Franz Verga]

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

 

[Guest]

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.