Disable Flickering screen when running macro

Microsoft Book: office excel 2003 inside...
October 1, 2009 at 22:10:57
Specs: Windows XP, pentium 4 3.00Ghz. 1gb ram
I have a code to copy paste rows from sheet1 to sheet2. But when running the code, i can see a rows being copy paste and excel screen goes on flickering till completion of copy paste. Can you provide a code to disable the flickering. The code goes like this:

Sub Copy()

Dim DestSheet As Worksheet
Set DestSheet = Worksheets("Sheet2")

Dim sRow As Long 'row index on source worksheet
Dim dRow As Long 'row index on destination worksheet
Dim sCount As Long
sCount = 0
dRow = 1

For sRow = 1 To Range("D100").End(xlDown).Row
Cells(sRow, "B").Copy
DestSheet.Cells(dRow, "B").PasteSpecial xlpasteValues

See More: Disable Flickering screen when running macro

Report •

October 2, 2009 at 02:36:08
Put this below SUB:
Application.ScreenUpdating = False

and put this above END:
Application.ScreenUpdating = True

Report •

October 2, 2009 at 04:51:04
While the ScreenUpdating suggestion will certainly work, I have a question:

Why are you looping through the copy? Can't you copy the entire range at one time?

Range("B1:B" & Range("D10").End(xlDown).Row).Copy

Report •

October 2, 2009 at 04:54:41

I suggest that you include an error trap which also reactivates the screen.

After Sub and before the first line of actual code:
On Error Goto ErrorHandler

At the end, after last line of code:

Exit Sub
Application.ScreenUpdating = True
End Sub

If your macro runs for some time you can use the following to put messages on the status bar, even when screen updating is turned off:

Application.StatusBar = "This is what is happening"


Report •

Related Solutions

October 2, 2009 at 09:02:46

It has been pointed out to me that when a macro ends, either normally or with an error, that screen updating is automatically reset.

As a result adding Application.ScreenUpdating = True to your error handler is not necessary.


Report •

October 2, 2009 at 21:34:28
Thank you lantere. That was what i was using but put it at wrong line.

Derby, there are some conditions attach with the copy paste. The lines of code i put above is just a part.

At last, Thanks Humar for your input which broaden my knowledge.

Report •

Ask Question