ScreenUpdating not working

[Otto Moehrbach]

Excel XP, Win XP
The code below is an abbreviation of my code just to illustrate my problem.
The code simply opens the "One.xls" file, copies from "One.xls" and
PasteSpecial xlPasteValues to file "Two.xls",
closes the "One.xls" file, and activates the "Two.xls" file.
The code works fine. No problem there.
The problem is that the screen jumps around during the Copy/Paste process
even though I have ScreenUpdating set to False.
Am I missing something with ScreenUpdating? Thanks for your time. Otto

Application.ScreenUpdating = False
Workbooks.Open Filename:=.."One.xls"
With Workbooks("Two.xls").Sheets("TheSht")
Cells.Copy
.Range("A1").PasteSpecial xlPasteValues
End With
Application.DisplayAlerts = False
ActiveWorkbook.Close SaveChanges:=False
Application.DisplayAlerts = True
Windows("Two.xls").Activate
Sheets("TheSht").Activate
Application.ScreenUpdating = True

 

[Dave Peterson]

There are some functions (mostly in the ATP, IIRC) that can turn the
screenupdating on.

Since you're opening a workbook, maybe there's something in there that toggles
the setting.

I'd add a few lines like:
Debug.print "Before Open--" & application.screenupdating
Workbooks.Open Filename:="One.xls"
Debug.print "after Open--" & application.screenupdating
.....

To determine what's turning it back on. I think the best you can do is turn it
back off as soon as you notice it.

By the way, I think your code would document itself better if you used:
activesheet.Cells.Copy

or even used a worksheet variable to represent that activesheet on that newly
opened workbook.

(I thought that you left off a dot on my initial reading.)

 

[Otto Moehrbach]

Dave
I appreciate that tip on documenting the code a bit better. I have
found it a bit challenging to figure out code I had written just a few
months ago.
I'll use your idea on chasing down the ScreenUpdating problem. Thanks
again. Otto

 

[Otto Moehrbach]

Dave
Here is what I found. It baffles me. The code is like:
Application.ScreenUpdating = False
If Dir(ThePath & "File Name") = "" Then
MsgBox ..........
Exit Sub
Else
MsgBox "Before ClearContents--" & Application.ScreenUpdating
Sheets("Imported Data").Cells.ClearContents
MsgBox "After ClearContents--" & Application.ScreenUpdating
------

 

[Dave Peterson]

Maybe turning off events before you clearcontents would prevent any
worksheet_change event from firing?????

Then turn them back on:

MsgBox "Before ClearContents--" & Application.ScreenUpdating
application.enableevents = false
Sheets("Imported Data").Cells.ClearContents
application.enableevents = true
MsgBox "After ClearContents--" & Application.ScreenUpdating

 

[Otto Moehrbach]

Dave
It amazes me how many good ideas you have. I think you have seen Excel
before <g>. Thanks for that. Otto

 

[Dave Peterson]

If it worked, you may have fixed the problem with the screen updating, but you
may have caused other stuff to go wrong--maybe that event was important???????

I'd double check--just in case.

Dave
It amazes me how many good ideas you have. I think you have seen Excel
before <g>. Thanks for that. Otto