Application.ScreenUpdating Question

ststern45

Well-known Member
Joined
Sep 17, 2005
Messages
814
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,473
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Aug 31, 2016
Messages
3,951
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

Well-known Member
Joined
Sep 17, 2005
Messages
814
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,473
Office Version
  1. 2019
Platform
  1. Windows
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

Rich (BB code):
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

Board Regular
Joined
Feb 18, 2020
Messages
206
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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:
VBA Code:
Dim bolScrnUpdating As Boolean
bolScrnUpdating = Application.ScreenUpdating
Application.ScreenUpdating = False
then, before exit from the sub, restore what it was on entry, like so:
VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,993
Messages
5,622,049
Members
415,875
Latest member
Tarali

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top