Refreshing external data

[JohnUK]

Hi, is there a piece of code that tells external data to refresh?
Basically – The workbook refreshes data when a number is changed (which
works well manually) I then copy this data onto another tab as values, back
to the main page, change to another number, copy the data onto a different
tab and so on up to 50 times. Because the external data takes up to 8 seconds
to refresh each time after a number is changed, you can imagine how long this
takes, so I naturally want to get this automated using a macro. I have
everything working well apart from the External data doesn’t want to refresh
whilst the macro is running. I have tried adding a pause of up to 15 seconds
allowing plenty of time for the refresh to work, but still nothing.
Second attempt for help, as its driving me up the wall.

 

[Tom Ogilvy]

Assuming this is a query table then
Change the backgroundquery property of the querytable to false or use it as
an argument of the refresh command.

 

[JohnUK]

Hi Tom, What would be the refresh command?
I have tried:
..enable events
..RefreshAll
The above came back as errors.
This is an extract of the code. Can you see by this where I am going wrong?

Dim num As Long
For num = 1 To 50
With Worksheets("Report")
.Range("X2").Value = .Range("X2").Value + 1 ’ This is where the
number would change
.Range("X4").Value = num
Application.Wait Now + TimeValue("00:00:10")
.Calculate
DoEvents
Application.Goto Reference:="All"
Selection.Copy
End With
Worksheets(num + 2).Range("B2").PasteSpecial xlValues
Range("A1").Select
Sheets("Report").Select

Regards
John