Refresh All and calc issue

AngAgius

Board Regular
Joined
Feb 12, 2004
Messages
59
I refresh data (MSQuery) using

ActiveWorkbook.RefreshAll

that's fine. I then add some calculations in the columns to the right and copy the formula down to the last row of data.

Range("U2:AJ2").Copy
Range("U2:AJ" & Range("b65536").End(xlUp).Row).Select
ActiveSheet.Paste

The issue I have is that the spreadsheet has not completed the refresh fully before the copy is executed and I'm getting errors on the spreadsheet.

It works fine when I step through the code, as I wait for the refresh to complete, but not when I fire the macro. I could make this a two stage process but it would be alot neater just to do the whole thing in one.

Any ideas?
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

chiello

Well-known Member
Joined
Jan 18, 2005
Messages
848
Ciao, try the following:

1) Disable Screen UpDating at the beginning of your Code and Re-Enable it at the end.

Is it enough?

Yes: OK :p

No: o_O 2) Stop Code execution the time necessary to refresh.

Below the code, where it is also suggested a more concise way to performe copy and paste.

<font face=Courier New><SPAN style="color:#007F00">'...</SPAN>
<SPAN style="color:#007F00">'First Attempt: disable Screen updating</SPAN>
Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#007F00">'...</SPAN>
ActiveWorkbook.RefreshAll
<SPAN style="color:#007F00">'Second Attempt: Time To Refresh is ... about 10s, stop code execution for 15s</SPAN>
Application.Wait (Now + TimeValue("0:00:15"))

<SPAN style="color:#007F00">'...</SPAN>

Range("U2:AJ2").Copy Destination:=Range("U2:AJ" & Range("b65536").End(xlUp).Row)

<SPAN style="color:#007F00">'...</SPAN>
<SPAN style="color:#007F00">'First Attempt: Enable Screen Updating (don't forget!!)</SPAN>
Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Post for feedback :wink:
 

AngAgius

Board Regular
Joined
Feb 12, 2004
Messages
59
thanks for the reply.

this also works:

uncheck "enable background refresh" in the data range properties.
 

Forum statistics

Threads
1,148,525
Messages
5,747,194
Members
424,068
Latest member
Salim khamis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top