Application.ScreenUpdating = False isn't working. Is it possible it is being turned on somewhere in my code or an MS365 issue?

Question

I recently downloaded Microsoft 365 as I switched company but now my application.screenupdating isn't working - it is painfully slow with this on. Is there potentially a work around? I have added my code below (and apologies for it not being a snippet) but otherwise I might miss the section where it is tripping it to turning back on. Any advice is hugely appreciated! It seems to work for a mac user who uses the same code...?

Sub Disaggregated_Data_Model()

'Alert box before running the model
    Dim Answer As VbMsgBoxResult
        Answer = MsgBox("Do you want to start the model?", vbYesNo + vbQuestion + vbDefaultButton2)
    If Answer = vbYes Then

'Turn off items that slow down the model
    Application.ScreenUpdating = False
    
'Clear prior disaggregated returns data
    With Worksheets("Network Returns")
        'Disag scenario reuslts
        .Range("J11:AF1009").ClearContents
        'Cash flows
        .Range("AI11:AL77").ClearContents
        .Range("AO11:AR77").ClearContents
        .Range("AU11:AX77").ClearContents
    End With

On Error Resume Next

'Run macro through scenario down
    Sheets("SITE Model").Range("C14").Value = "Down"

'Clear prior model data
    With Worksheets("NETWORK Model")
        .Range("D24:EF28").ClearContents
    End With
    
On Error GoTo 0

'Set site model ready for DB run
    With Worksheets("SITE Model")
        .Range("C10").Value = "DB #"
        .Range("C11").Value = 0
    End With
    
'Row movement variable
RowNum = 11
    
'Run macro through specified start site index to end site index
    For i = Sheets("Scenario Selector").Range("K10") To Sheets("Scenario Selector").Range("K11")
        Sheets("SITE Model").Range("C11").Value = i
        
        'Capture data from SITE Model
            Sheets("SITE Model").Range("C283:C289").Copy
            Sheets("Network Returns").Range("J" & RowNum).PasteSpecial xlPasteValues, Transpose:=True

            'Move paste down 1 row on each cycle
                RowNum = RowNum + 1

        'Aggregate building copy paste
            Sheets("NETWORK Model").Range("D34:EF38").Copy
            Sheets("NETWORK Model").Range("D24").PasteSpecial xlPasteValues
    Next
    
'Paste downside aggregate returns for DA
    Sheets("NETWORK Model").Range("C65:C72").Copy
    Sheets("Network Returns").Range("C12:C19").PasteSpecial xlPasteValues
    
'Paste all aggrgeated cash flows into Network Returns
    Sheets("NETWORK Model").Range("D76:EF79").Copy
    Sheets("Network Returns").Range("AI11").PasteSpecial xlPasteValues, Transpose:=True
        
'Paste cumulative cash flow sums for strike 10 and site owner
    Sheets("NETWORK Model").Range("EG78:EG79").Copy
    Sheets("Network Returns").Range("C84").PasteSpecial xlPasteValues

'Run macro through scenario base
    Sheets("SITE Model").Range("C14").Value = "Base"

'Clear prior model data
    With Worksheets("NETWORK Model")
        .Range("D24:EF28").ClearContents
    End With
    
On Error GoTo 0

'Set site model ready for DB run
    With Worksheets("SITE Model")
        .Range("C10").Value = "DB #"
        .Range("C11").Value = 0
    End With
    
'Row movement variable
RowNum1 = 11

'Run macro through specified start site index to end site index
    For i = Sheets("Scenario Selector").Range("K10") To Sheets("Scenario Selector").Range("K11")
        Sheets("SITE Model").Range("C11").Value = i
        
        
        'Capture data from SITE Model
            Sheets("SITE Model").Range("C283:C289").Copy
            Sheets("Network Returns").Range("R" & RowNum1).PasteSpecial xlPasteValues, Transpose:=True

            'Move paste down 1 row on each cycle
                RowNum1 = RowNum1 + 1

        'Aggregate building copy paste
            Sheets("NETWORK Model").Range("D34:EF38").Copy
            Sheets("NETWORK Model").Range("D24").PasteSpecial xlPasteValues
    Next
    
'Paste base aggregate results for DA
    Sheets("NETWORK Model").Range("C65:C72").Copy
    Sheets("Network Returns").Range("D12:D19").PasteSpecial xlPasteValues
   
'Paste all aggrgeated cash flows into scenario database
    Sheets("NETWORK Model").Range("D76:EF79").Copy
    Sheets("Network Returns").Range("AO11").PasteSpecial xlPasteValues, Transpose:=True
    
'Paste cumulative cash flow sums for strike 10 and site owner
    Sheets("NETWORK Model").Range("EG78:EG79").Copy
    Sheets("Network Returns").Range("D84").PasteSpecial xlPasteValues

'Run macro through scenario up
    Sheets("SITE Model").Range("C14").Value = "Up"

'Clear prior model data
    With Worksheets("NETWORK Model")
        .Range("D24:EF28").ClearContents
    End With
    
On Error GoTo 0

'Set site model ready for DB run
    With Worksheets("SITE Model")
        .Range("C10").Value = "DB #"
        .Range("C11").Value = 0
    End With
    
'Row movement variable
RowNum2 = 11
    
'Run macro through specified start site index to end site index
    For i = Sheets("Scenario Selector").Range("K10") To Sheets("Scenario Selector").Range("K11")
        Sheets("SITE Model").Range("C11").Value = i
        
        'Capture data from SITE Model
            Sheets("SITE Model").Range("C283:C289").Copy
            Sheets("Network Returns").Range("Z" & RowNum2).PasteSpecial xlPasteValues, Transpose:=True

            'Move paste down 1 row on each cycle
                RowNum2 = RowNum2 + 1

        'Aggregate building copy paste
            Sheets("NETWORK Model").Range("D34:EF38").Copy
            Sheets("NETWORK Model").Range("D24").PasteSpecial xlPasteValues
    Next
    
'Paste up aggregate results for DA
    Sheets("NETWORK Model").Range("C65: C72 ").Copy
    Sheets("Network Returns").Range("E12:E19").PasteSpecial xlPasteValues
    
'Paste all aggrgeated cash flows into scenario database
    Sheets("NETWORK Model").Range("D76:EF79").Copy
    Sheets("Network Returns").Range("AU11").PasteSpecial xlPasteValues, Transpose:=True
    
'Paste cumulative cash flow sums for strike 10 and site owner
    Sheets("NETWORK Model").Range("EG78:EG79").Copy
    Sheets("Network Returns").Range("E84").PasteSpecial xlPasteValues

'Time stamp
    Sheets("Network Returns").Range("C3") = Now
    
    Else
    Exit Sub
    End If

'Exit out of copy paste mode
    Application.CutCopyMode = False
    
'Go to results page
    Sheets("NETWORK Model").Activate
    Range("A1").Select
    Sheets("Network Returns").Activate
    Range("A1").Select
    
'Turn screen updates back on
    Application.ScreenUpdating = True
    
'Alert for done
    MsgBox "Scenario testing complete"

End Sub

Sub Returns_Only_Model()

'Alert box before running the model
    Dim Answer As VbMsgBoxResult
        Answer = MsgBox("Do you want to start the model?", vbYesNo + vbQuestion + vbDefaultButton2)
    If Answer = vbYes Then

'Turn off items that slow down the model
    Application.ScreenUpdating = False
    
'Clear prior disaggregated returns data
    With Worksheets("Network Returns")
        'Disag scenario reuslts
        .Range("J11:AF1009").ClearContents
        'Cash flows
        .Range("AI11:AL77").ClearContents
        .Range("AO11:AR77").ClearContents
        .Range("AU11:AX77").ClearContents
    End With

On Error Resume Next

'Run macro through scenario down
    Sheets("SITE Model").Range("C14").Value = "Down"

'Clear prior model data
    With Worksheets("NETWORK Model")
        .Range("D24:EF28").ClearContents
    End With
    
On Error GoTo 0

'Set site model ready for DB run
    With Worksheets("SITE Model")
        .Range("C10").Value = "DB #"
    End With
    
'Run macro through specified start site index to end site index
    Dim i As Integer
    For i = Sheets("Scenario Selector").Range("K10") To Sheets("Scenario Selector").Range("K11")
        Sheets("SITE Model").Range("C11").Value = i

        'Aggregate building copy paste
            Sheets("NETWORK Model").Range("D34:EF38").Copy
            Sheets("NETWORK Model").Range("D24").PasteSpecial xlPasteValues
    Next i
    
'Paste downside aggregate returns for DA
    Sheets("NETWORK Model").Range("C65:C72").Copy
    Sheets("Network Returns").Range("C12:C19").PasteSpecial xlPasteValues
    
'Paste all aggrgeated cash flows into scenario database
    Sheets("NETWORK Model").Range("D76:EF79").Copy
    Sheets("Network Returns").Range("AI11").PasteSpecial xlPasteValues, Transpose:=True
    
'Paste cumulative cash flow sums for strike 10 and site owner
    Sheets("NETWORK Model").Range("EG78:EG79").Copy
    Sheets("Network Returns").Range("C84").PasteSpecial xlPasteValues
    
'Run macro through scenario down
    Sheets("SITE Model").Range("C14").Value = "Base"

'Clear prior model data
    With Worksheets("NETWORK Model")
        .Range("D24:EF28").ClearContents
    End With
    
On Error GoTo 0

'Set site model ready for DB run
    With Worksheets("SITE Model")
        .Range("C10").Value = "DB #"
        .Range("C11").Value = 1
    End With
    
'Run macro through specified start site index to end site index
    For i = Sheets("Scenario Selector").Range("K10") To Sheets("Scenario Selector").Range("K11")
        Sheets("SITE Model").Range("C11").Value = i

        'Aggregate building copy paste
            Sheets("NETWORK Model").Range("D34:EF38").Copy
            Sheets("NETWORK Model").Range("D24").PasteSpecial xlPasteValues
    Next
    
'Paste base aggregate returns for DA
    Sheets("NETWORK Model").Range("C65:C72").Copy
    Sheets("Network Returns").Range("D12:D19").PasteSpecial xlPasteValues
    
'Paste all aggrgeated cash flows into scenario database
        Sheets("NETWORK Model").Range("D76:EF79").Copy
        Sheets("Network Returns").Range("AO11").PasteSpecial xlPasteValues, Transpose:=True
        
'Paste cumulative cash flow sums for strike 10 and site owner
    Sheets("NETWORK Model").Range("EG78:EG79").Copy
    Sheets("Network Returns").Range("D84").PasteSpecial xlPasteValues
    
'Run macro through scenario down
    Sheets("SITE Model").Range("C14").Value = "Up"

'Clear prior model data
    With Worksheets("NETWORK Model")
        .Range("D24:EF28").ClearContents
    End With
    
On Error GoTo 0

'Set site model ready for DB run
    With Worksheets("SITE Model")
        .Range("C10").Value = "DB #"
        .Range("C11").Value = 1
    End With
    
'Run macro through specified start site index to end site index
    For i = Sheets("Scenario Selector").Range("K10") To Sheets("Scenario Selector").Range("K11")
        Sheets("SITE Model").Range("C11").Value = i

        'Aggregate building copy paste
            Sheets("NETWORK Model").Range("D34:EF38").Copy
            Sheets("NETWORK Model").Range("D24").PasteSpecial xlPasteValues
    Next
    
'Paste up aggregate returns for DA
    Sheets("NETWORK Model").Range("C65:C72").Copy
    Sheets("Network Returns").Range("E12:E19").PasteSpecial xlPasteValues
    
'Paste all aggrgeated cash flows into scenario database
        Sheets("NETWORK Model").Range("D76:EF79").Copy
        Sheets("Network Returns").Range("AU11").PasteSpecial xlPasteValues, Transpose:=True

'Paste cumulative cash flow sums for strike 10 and site owner
    Sheets("NETWORK Model").Range("EG78:EG79").Copy
    Sheets("Network Returns").Range("E84").PasteSpecial xlPasteValues

'Time stamp
    Sheets("Network Returns").Range("C3") = Now
    
    Else
    Exit Sub
    End If

'Exit out of copy paste mode
    Application.CutCopyMode = False
    
'Go to results page
    Sheets("NETWORK Model").Activate
    Range("A1").Select
    Sheets("Network Returns").Activate
    Range("A1").Select
    
'Turn screen updates back on
    Application.ScreenUpdating = True
    
'Alert for done
    MsgBox "Scenario testing complete"

End Sub

Thanks for all your help!

Is the screen still updating while code is running, or is it working (by not updating) but taking a long time to complete? have you swtiched to a slower PC? There are alot of areas where your code can be optimized, see [how-to-avoid-using-select-in-excel](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?rq=1) instead of copy/pasting everything, just set the range = the other range. — Daghan, Jul 15 '20 at 12:12
I second what @Daghan said (there's a big optimisation opprotunity in your code). However, your code as it is should not be **painfully** slow. I suspect you have code triggered by events somewhere or some udf functions calculating every time you paste values. Try `Application.EnableEvents = False` and `Application.Calculation = xlCalculationManual` along with your `ScreenUpdating`. and don't forget to reset them at the end. With this I believe you'll see a significant improvement. — Super Symmetry, Jul 15 '20 at 12:44
Furthermore, istead of copying and pasting values try something like `myRange1.Value = myRange2.Value` and to transpose try `myRange1.Value = WorksheetFunction.Transpose(myRange2.Value)` — Super Symmetry, Jul 15 '20 at 12:47

Application.ScreenUpdating = False isn't working. Is it possible it is being turned on somewhere in my code or an MS365 issue?

0 Answers0