Screen update or visible?

[Paladin046]

i have a macro that calculates values in one sheet then transfers
values to another sheet. The calculations are done based on a column
of data and a for-next loop. This works fine but i find that the
screen flashes between the two sheets while the macro is running and I
find this very annoying. I have tried making the sheets not visible
(Worksheet("sheet1").visible=false) then making it visible at the end.
this didn't help. I tried stopping the screen updating
(Application.ScreenUpdating = False) then reversing the command
(Application.ScreenUpdating = True) but that caused other problems.
Is ther a simple way to stop seeing the flashing between sheets?

i have searched past postings in this forum but either I am not
searching correctly or the posting I did find didn't solve my problem.

 

[Dave Peterson]

I don't know what problems .screenupdating caused for you, but I can't recall
ever seeing any problems when I've used those lines.

And maybe you could re-write your code so that it doesn't do any select's or
activate's???

 

[Paladin046]

I don't know what problems .screenupdating caused for you, but I can't recall
ever seeing any problems when I've used those lines.

And maybe you could re-write your code so that it doesn't do any select'sor
activate's???

Dave,

Not using select or activate would totally kill the utility of the
macro.
When I use the Application.ScreenUpdating method I see that data does
not get transferred to the required sheet. I thought that the screen
would update when I switched back to the updating mode but since that
was outside the for next loop, I realized that I was very wrong.

I ran across a reference to "xlVeryHidden" which I need to explore to
see if that would work.

 

[Dave Peterson]

Instead of code like:

Sheets("Sheet1").Select
Range("A1:B3").Select
Selection.Copy
Sheets("Sheet2").Select
Range("F13").Select
ActiveSheet.Paste

You could use:

worksheets("Sheet1").Range("A1:B3").Copy _
destination:=worksheets("Sheet2").range("F13")

Then you won't be selecting/activating stuff and the flickering should disappear.

But I still don't know what kind of code would cause the macro to fail with the
..screenupdating set to false.

Maybe you could share that portion of the code that fails.

 

[Paladin046]

Instead of code like:

     Sheets("Sheet1").Select
     Range("A1:B3").Select
     Selection.Copy
     Sheets("Sheet2").Select
     Range("F13").Select
     ActiveSheet.Paste

You could use:

     worksheets("Sheet1").Range("A1:B3").Copy _
         destination:=worksheets("Sheet2").range("F13")

Then you won't be selecting/activating stuff and the flickering should disappear.

But I still don't know what kind of code would cause the macro to fail with the
.screenupdating set to false.

Maybe you could share that portion of the code that fails.

Dave,

I'll look and see if I still have the offending code. I may have
deleted it as it didn't work. I'll send also some of the selection
methods for your review.

Garry