Excel is running too fast

[Elton Law]

Dear Expert,
I need to copy certain fields from one sheet to a sheet called "conso" as a
summary page.
All duties are easy.
Just copy and paste.
I preset in macro to use a value "23" in cell A266 ... then formula in other
cells in the sheet will run and calculate to give the final figures ...
Copy A267:E272 to conso sheet .... Copy M267:M272 to conso sheet .. Copy
BP267:BQ272 .... to conso sheet
Then, replace the value "27" in cell A266 (same cell), then formula in other
cells will run and calculate again ....
Repeat the same jobs ...."Copy A267:E272 to conso sheet .... Copy M267:M272
to conso sheet .. Copy BP267:BQ272 .... to conso sheet"
Finally, replace "49" in cell A266 (same cell), then formula in other cells
will run and calculate again.
Same duty ...."Copy A267:E272 to conso sheet .... Copy M267:M272 to conso
sheet .. Copy BP267:BQ272 .... to conso sheet"
Actually, replacing the values via macro in cell A266 is very easy.

I set changing the value 20 times in cell A266.
Say. 23,27,49,53,57,89,112 ...etc ..etc ...
Do the same things ..."Copy A267:E272 to conso sheet .... Copy M267:M272 to
conso sheet .. Copy BP267:BQ272 .... to conso sheet"
Look like all should be alright.

The problem is that ....
I used "Calculate" or "ActiveWorkbook.Calculate" in VB scripts to refresh
all formula and thus values before copying over to conso sheet...

But in the end, after changing the values 6 times in cell A266, the rest of
14 outcomes are same as 6th value data.

I used Application.Wait (now + ("0:00:10")) in order to hold a wait before
copying.
I found EXCEL was stale rather than refreshing formulas/data....

So what should I write in VB scripts to make sure all cells are fully
re-calculated (refresh) whenever I input a new value in cell A266 before I
copy something over to conso sheet please ?

Thanks

 

[NickH]

Hi Elton

You should not need to re-calculate after each value change in you
controlling cell.

A couple of questions:
1. Does the destination range in your conso sheet changes for each iteration
of the loop?
2. Are you pasting values in the conso sheet? I'm thinking about formula
references to the original (source) range being retained.

I can't think of anything else to check at present, but there are too many
unknowns here to work out a solution. It sounds to me as if you need to
focus on the destination, rather than the source from what you describe.

Nick H

 

[Elton Law]

Hi Nick,
Actually, copy and paste values from a sheet to conso sheet.
But the value in sheet are certainly a complicated formula which takes
sometime to calculate.
Dependent Variables is in cell A266.
When I input a figure in cell A266, calculation will go on and take few
seconds to work .... Then I will copy the answer and paste values to conso
sheet only.
Honestly, duties are clerical and easy.
But Excel calculation is too slow or copy/paste is too fast that it takes
action even formula hasn't come out the final outcome.
So want to have some VB scripts rather than "wait" or "calculate" (as I try
before) that can allow Excel to refreash all data before going to next steps
in scripts.
Thanks

 

[NickH]

Hi Elton

I don't think the time taken in the calculation has any material effect for
the symptoms you describe. From a code point of view, the macro will not
advance to the next instruction until the previous action has completed.

You could add a DoEvents statement between iterations of the loop, but I
doubt that that will make any difference. This yields control to the
operating system to give it a chance to handle other tasks.

You answered my question about values, but not the one about the output
range in "conso". How do you change the output destination for each iteration
of the loop? Can you give me an example of your code for the copy, say for
range A267:E272, please?

Nick