Application.ScreenUpdating Question

[ststern45]

Hello everyone,

This has probably been asked here many times.
I have 1 workbook that contains worksheets for each day of the month of July 2020
I have another worksheet 'sheet1" that I use a macro to transfer data from the dated (July) worksheets.
The macro works like this:
"sheet1" has an area where I copy and paste to a cell range. Before it copies the data from a selected month worksheet the macro goes to the worksheet and runs additional macros to calculate data. Once the calculations are completed the macro copies the data back to the "sheet1" worksheet.

Everything works great except 1 thing. I tried entering the 2 lines of code in the proper place:

Application.ScreenUpdating = False

Application.ScreenUpdating = True

Unfortunately the 2 lines of code will not keep all the calculations on the "sheet1" worksheet. Meaning the macro runs, it goes to the Jul222020 worksheet then goes back to the "sheet1" worksheet. Then back and forth until macro completes all the calculations.

Any help would be greatly appreciated.

Thank you.

 

[dmt32]

Hi,
in most cases, you do not need to select a sheet for code to work with a non active sheet.

if you post your code, plenty here to show you how to change it to eliminate the problem you describe.

Dave

 

[Logit]

Do you obtain the correct results whether you are using the SCREENUPDATING or not ?

If the answer is yes, then it makes no difference.

 

[ststern45]

This is the code:
Sub GuessMirrorReport1()

Dim aRng As String



aRng = Sheets("GuessMirror").Range("I1").Value

With ActiveSheet
For Each cll In .Range(aRng & Range("I2").Value & ":" & aRng & Range("I3").Value).Cells

.Range("I4").Value = cll.Value


GuessMirrorReport2


Next cll
End With



End Sub


Sub GuessMirrorReport2()

Application.ScreenUpdating = False

Dim sRng As Long, Sname1 As String, Sname2 As String
Dim eRng As Long




Sname1 = Sheets("GuessMirror").Range("B5").Value
Sname2 = Sheets("GuessMirror").Range("K6").Value


Sheets(Sname1).Select

Application.Run "Pick3Pick4CubeBook.xlsm!" & Sname2


Range("E64:G64").Select
Application.CutCopyMode = False
Selection.Copy


Sheets("GuessMirror").Select

Range("Q" & Range("K7").Value).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False



Sheets(Sname1).Select

Range("N64:P64").Select
Application.CutCopyMode = False
Selection.Copy


Sheets("GuessMirror").Select

Range("U" & Range("K7").Value).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False



Sheets(Sname1).Select

Range("BF64:BI64").Select
Application.CutCopyMode = False
Selection.Copy


Sheets("GuessMirror").Select

Range("AA" & Range("K7").Value).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False



Sheets(Sname1).Select

Range("BP64:BS64").Select
Application.CutCopyMode = False
Selection.Copy


Sheets("GuessMirror").Select

Range("AF" & Range("K7").Value).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False


Range("N1").Select


Application.ScreenUpdating = True


End Sub

 

[dmt32]

Hi,

Part of your problem with ScreenUpdating issue is that you are turning it Off Then back On In the called code hence I suspect, the reason for flashing. Placing ScreenUpdating in the main code may reduce this issue.

try re-locating ScreenUpdating property where shown below

Sub GuessMirrorReport1()
    Dim aRng As String
  
    aRng = Sheets("GuessMirror").Range("I1").Value
   
    Application.ScreenUpdating = False
   
    With ActiveSheet
        For Each cll In .Range(aRng & Range("I2").Value & ":" & aRng & Range("I3").Value).Cells
           
            .Range("I4").Value = cll.Value
           
            GuessMirrorReport2
           
        Next cll
    End With
       
    Application.ScreenUpdating = True
       
End Sub

However, when referring to a range or individual cells in a non-active worksheet or worksheet that is not in the active workbook, it is better to fully qualify the reference rather than using select or activate in code - this approach should run faster & incidents of screen flash reduced / eliminated.

Hope helpful

Dave

 

[CephasOz]

To stop this flashing effect when you have subs calling other subs, at the top of each sub always store the current status before turning it off, like so:

Dim bolScrnUpdating As Boolean
bolScrnUpdating = Application.ScreenUpdating
Application.ScreenUpdating = False

then, before exit from the sub, restore what it was on entry, like so:

Application.ScreenUpdating = bolScrnUpdating

That way, if Screen Updating is off when a sub is called, it will stay off, if it's on, it will be turned off while the sub does its work, then turned back on at the end. No more flashing.