Help with VBA and external data

[ϵpsilon]

Hi

I've got some external data in cell M7 and need to copy the values to another cell. I've recorded a macro that refreshes the external data and then copies and pastes the values. When I run the macro it copies and pastes the old values while the external data is refreshing.

The code look like this:

Sub Macro2()
'
' Macro2 Macro
'


'
Application.CutCopyMode = False
ActiveWorkbook.Connections("Connection").Refresh
Range("M7:N7").Select
Selection.Copy
Range("D7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

Can anyone help me with this. I've tried adding Calculate before range select and I also tried application wait but it doesn't fix the problem.

 

[MrsPen]

I had this exact same problem and it drove me NUTS!! The problem is that your Macro runs faster than your refresh. I use .WaitHostSettle(1)
That will make you macro slow down for a second and wait for your external data to update. You may need to do this several times.
Good luck!

 

[ϵpsilon]

I had this exact same problem and it drove me NUTS!! The problem is that your Macro runs faster than your refresh. I use .WaitHostSettle(1)
That will make you macro slow down for a second and wait for your external data to update. You may need to do this several times.
Good luck!

Thanks for your help. I will try that. I also found that going to data properties and turning off enable background refresh gives it time to update before copying.