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 doesnt 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.

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.

--
Regards,
Tom Ogilvy


"JohnUK" wrote:

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 doesnt 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.

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


"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy


"JohnUK" wrote:

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 doesnt 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.