VBA: Force wait until recalculate

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I've got a dashboard which is batch produced for all staff.

It works on the following principle:
1 Change member of staff
2 Copy sheets into a new file
3 Save new file with staff name

There's an assumed step 1b - I expect excel to recalculate its formulas
based on the name (lookups to queries etc), but recalculating takes a little
while.

I'm finding that it proceeds to step 2 (pastes the data out) before its done
the recalculation.

I understand there's a Calculate command in VBA, and wonder what I can add
to it to force everything to wait until the calculate process is 100%
complete.

Thanks in advance!

Tom.
 
Hi
It may not work but try putting in the line
DoEvents

before the sheet copy code. I find a smattering of these in code helps
when there are screen refreshes or other graphic updates going on.
regards
Paul
 
Thanks, Paul.

I've chucked in an Application.CalculateFull and that's currently being
tested.

DoEvents will be next.

Cheers,

Tom.
 
The forced calculate did the job, but the Do Events would have done just as
well.

Cheers.

Tom.
 
Back
Top