VBA: Force wait until recalculate
 

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.

VBA: Force wait until recalculate
 

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

On May 4, 10:04 am, mr tom <mr-tom at mr-tom.co.uk.(donotspam) wrote:
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.

VBA: Force wait until recalculate
 

Thanks, Paul.

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

DoEvents will be next.

Cheers,

Tom.

" wrote:

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

On May 4, 10:04 am, mr tom <mr-tom at mr-tom.co.uk.(donotspam) wrote:
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.

VBA: Force wait until recalculate
 

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

Cheers.

Tom.

"mr tom" wrote:

Thanks, Paul.

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

DoEvents will be next.

Cheers,

Tom.

" wrote:

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

On May 4, 10:04 am, mr tom <mr-tom at mr-tom.co.uk.(donotspam) wrote:
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.