Hello,
I have a macro in excel 2003 that reads a value from a cell on one
worksheet, processes it, and records the result into a cell on another
worksheet. The trouble is, it does this for hundreds of cells and when I
run the macro the spreadsheet flicks between the sheets until the macro
terminates. This looks very ugly.

Is it possible to stop the screen updates at the beginning of the macro
and then, when all the calculations have been done, let the screen be
updated?

I hope this makes sense.

thank you

try something like this...

Sub Whatever()
Application.ScreenUpdating = False
'Your code
Application.ScreenUpdating = True
End Sub
--
HTH...

Jim Thomlinson


"none" <""john"@(none)" wrote:

> Hello,
> I have a macro in excel 2003 that reads a value from a cell on one
> worksheet, processes it, and records the result into a cell on another
> worksheet. The trouble is, it does this for hundreds of cells and when I
> run the macro the spreadsheet flicks between the sheets until the macro
> terminates. This looks very ugly.
>
> Is it possible to stop the screen updates at the beginning of the macro
> and then, when all the calculations have been done, let the screen be
> updated?
>
> I hope this makes sense.
>
> thank you
>

On 4 Oct, 22:02, none <""john\"@(none)"> wrote:
> Hello,
> I have a macro in excel 2003 that reads a value from a cell on one
> worksheet, processes it, and records the result into a cell on another
> worksheet. The trouble is, it does this for hundreds of cells and when I
> run the macro the spreadsheet flicks between the sheets until the macro
> terminates. This looks very ugly.
>
> Is it possible to stop the screen updates at the beginning of the macro
> and then, when all the calculations have been done, let the screen be
> updated?
>
> I hope this makes sense.
>
> thank you

Hello None

I would suggest that using object variables would resolve this. That
way, you don't have to select the cell that you're either reading from
or writing to - hence no flicker!

Assuming that you're reading from Sheet1 and writing to Sheet3, and
that you're just doing a simple squaring of the value (just to take an
example) your code might look something like this:

Regards
Andrew

PS Are you really a Nun? Just kidding ;-)


Sub TransformValues()
Dim rngSource As Range, rngDest As Range
Dim sngCalcAnswer As Single
Dim i As Integer

'Get the original data from A1 on Sheet1
Set rngSource = Sheets("Sheet1").Range("A1")

'Put the answer in A1 on Sheet3
Set rngDest = Sheets("Sheet3").Range("A1")

'Use i to offset the source and destinations
'so that we move down the rows for calculations

'Continue calculating until there is no more data.
Do Until Len(rngSource.Offset(i).Value) = 0

'Modify the next line to include your desired calculations
sngCalcAnswer = rngSource.Offset(i).Value ^ 2

'Enter the answer on Sheet3
rngDest.Offset(i).Value = sngCalcAnswer

'Don't forget to increment i to look at the next row
i = i + 1
Loop

End Sub