Application.Screen Updating = False NOT WORKING

[kk4074]

Hello,

I have written a simple macro to record the results of a MonteCarlo simulation. The macro copies the results from each calculation and pastes them on a "results" worksheet where eventually the results will be analyzed. This macro processes the calculation 1,000 times and I would like for the user to be unaware of the process going on behind the scenes (i.e. no screen flickering). I have used a variation of this macro before with no screen flickering but this one doesn't seem to be working. I can still see Excel pasting the results on the "results" worksheet. I have inserted the code below...any help would be much appreciated.

Thanks!

Sub MonteCarlo()

    Application.ScreenUpdating = False
    Dim i, iMax As Integer
    iMax = 1000

    Sheet4.Range("B2:E1001").ClearContents 'Clear existing contents of results area
    iMax = iMax + 1
            
    For i = 1 To iMax 'Run the number of iterations the user specifies
                
                Application.ScreenUpdating = False   'Turn off screen updating to speed up process
                Sheet1.Calculate 'Calculate the "calculation" sheet to generate new random results
                    'Copy each of the imporant results and paste them on the results page
                Sheet1.Range("NPV").Copy
                Sheet4.Cells(i + 1, 2).PasteSpecial Paste:=xlPasteValues, _
                      Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                      Application.CutCopyMode = False
                Sheet1.Range("IRR").Copy
                Sheet4.Cells(i + 1, 3).PasteSpecial Paste:=xlPasteValues, _
                      Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                      Application.CutCopyMode = False
                Sheet1.Range("ROI").Copy
                Sheet4.Cells(i + 1, 4).PasteSpecial Paste:=xlPasteValues, _
                      Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                      Application.CutCopyMode = False
                Sheet1.Range("Payback").Copy
                Sheet4.Cells(i + 1, 5).PasteSpecial Paste:=xlPasteValues, _
                      Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                Application.CutCopyMode = False
            Next i
    
    Application.ScreenUpdating = True 'Turn screen updating back on to allow user to see results
    
End Sub

 

[p45cal]

try (untested):

Sub MonteCarlo()
Application.ScreenUpdating = False
Dim i, iMax As Integer
iMax = 1000

Sheet4.Range("B2:E1001").ClearContents    'Clear existing contents of results area
iMax = iMax + 1

For i = 1 To iMax    'Run the number of iterations the user specifies
    Sheet1.Calculate    'Calculate the "calculation" sheet to generate new random results
    'Copy each of the imporant results and paste them on the results page
    Sheet4.Cells(i + 1, 2).Resize(Sheet1.Range("NPV").Rows.Count, Sheet1.Range("NPV").Columns.Count).Value = Sheet1.Range("NPV").Value
    Sheet4.Cells(i + 1, 3).Resize(Sheet1.Range("IRR").Rows.Count, Sheet1.Range("IRR").Columns.Count).Value = Sheet1.Range("IRR").Value
    Sheet4.Cells(i + 1, 4).Resize(Sheet1.Range("ROI").Rows.Count, Sheet1.Range("ROI").Columns.Count).Value = Sheet1.Range("ROI").Value
    Sheet4.Cells(i + 1, 5).Resize(Sheet1.Range("Payback").Rows.Count, Sheet1.Range("Payback").Columns.Count).Value = Sheet1.Range("Payback").Value
Next i
Application.ScreenUpdating = True    'Turn screen updating back on to allow user to see results
End Sub

If you still get a flicker or two, try removing all Application.ScreenUpdating lines.

 

[kk4074]

p45cal,

Thanks for your response and attempt at solving my flickering frustration. I am inclined to believe that the issue is more a result of the computer I am using and the network environment than the macro itself.

But, I did use your macro to create a new contiguous range and simplify my code to 10 lines instead of the original 20 or so, so thank you!

If you (or anyone else) have any additional suggestions, I welcome them!

Thanks!