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.

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

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

"Mark Ivey" wrote:

> 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" <(E-Mail Removed)> wrote in message
news:4D3EEA5D-5472-4C20-82C0-(E-Mail Removed)...

> 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)


--
roger

This worked perfectly! Thank you.

"roger" wrote:

> "Graeme" <(E-Mail Removed)> wrote in message
> news:4D3EEA5D-5472-4C20-82C0-(E-Mail Removed)...
>
> > 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)
>
>
> --
> roger
>
>
>