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
--
Nick
"Elton Law" wrote:
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