Application wait question

[Graeme]

I'm having some issues with pausing a macro to allow data to load. I have a
table created from an imported web query, and my macro refreshes the query
and then makes some adjustments to the new data:

Sub NewData()

'Refresh part
Range("e44").Select
Selection.QueryTable.Refresh

'line X

'Adjust part
Range("ColA").Select
Selection.Copy
Range("ColB").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("ColB").Select
Selection.Replace What:="~*", Replacement:=""
End sub

For line X, I have tried:
Application.Wait TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 10)
and
Application.Wait Now + TimeValue("00:00:10")
and have also made use of:
Public Function Delay()
y = Timer + 10
Do While Timer < y
DoEvents
Loop
End Function

However, when I run the macro, only the Refresh part works even though it
takes 10 seconds to run, but when I run the macro again, the Adjust part then
works. Also, when I step through the data, the pause activates, and
everything seems to work. Any ideas would be appreciated. thanks.

 

[Mark Ivey]

Just a thought... still not sure if this will work... but have you tried
separating the macros? Please see example below.


Mark Ivey



Sub NewData()

'Refresh part
Range("e44").Select
Selection.QueryTable.Refresh

' Call the second macro
Second_Macro

End Sub


Sub Second_Macro()

'Adjust part
Range("ColA").Select
Selection.Copy
Range("ColB").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("ColB").Select
Selection.Replace What:="~*", Replacement:=""
End Sub

 

[Graeme]

Mark - thanks for responding. However, I had already tried that, and
unfortunately it didn't work either.

 

[roger]

I'm having some issues with pausing a macro to allow data to load. I have a
table created from an imported web query, and my macro refreshes the query
and then makes some adjustments to the new data:

Sub NewData()

'Refresh part
Range("e44").Select
Selection.QueryTable.Refresh

Does this work?

Selection.QueryTable.Refresh (False)

 

[Graeme]

This worked perfectly! Thank you.

Does this work?

Selection.QueryTable.Refresh (False)