Why does Selection.PasteSpecial toggle Application.ScreenUpdating
 

What version of xl are you using?

I can't emulate the problem in either xl2007 or xl2002.

I copied your code exactly and renamed a worksheet to Viewer and even
inserted a volatile formula with RANDBETWEEN in M11 and there is definitely
no screen updating until after the code is finished.

I tested with Application.ScreenUpdating = False inserted both before and
after the worksheet selection line.

Can you see if Range("M13:M204") is actually updated while the Point 2
msgbox is still displayed?

--
Regards,

OssieMac

Why does Selection.PasteSpecial toggle Application.ScreenUpdating
 

I am running Microsoft Excel 2003 SP3 with Windows XP Pro SP2. Yes, the
range is actually being updated.

OssieMac wrote:
What version of xl are you using?

I can't emulate the problem in either xl2007 or xl2002.

I copied your code exactly and renamed a worksheet to Viewer and even
inserted a volatile formula with RANDBETWEEN in M11 and there is definitely
no screen updating until after the code is finished.

I tested with Application.ScreenUpdating = False inserted both before and
after the worksheet selection line.

Can you see if Range("M13:M204") is actually updated while the Point 2
msgbox is still displayed?


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200908/1

Why does Selection.PasteSpecial toggle Application.ScreenUpdat
 

For interest I Googled 'application.screenupdating not working' and got
113,000 hits so you are obviously not on your own. Of the few that I looked
at, the only one that had any kind of solution was where the OP said he/she
deleted the line of code
(Application.ScreenUpdating = False) and re-inserted it and it worked
properly.

However, you might be better to adopt a more professional approach to your
code and avoid selecting worksheets or ranges. The only time your code should
select a worksheet or range is at the end of the code to leave the workbook
in a particular state displaying worksheet and range that the user is to
continue with.

I like to test my code by adding a dummy worksheet and selecting it before
running the code. I think I have it correct when the entire code runs without
ever moving off the dummy worksheet.

Try the following code in lieu of your posted code. Note that a space and
underscore at the end of a line is a line break in anotherwise single line of
code.

With Worksheets("Viewer")
.Range("M11").Copy
.Range("M13:M204").PasteSpecial _
Paste:=xlPasteFormulas, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
End With

--
Regards,

OssieMac