Running macros in Excel 2016 - screen flickering

[kit99]

I've been running the same macros for years now, and it's been working perfectly in both Excel 2007 and 2010.
But now my job's updated to 2016, and when I try to run the same macro the screen is flickering.
When speed isn't a issue, I prefer to have "screenupdating = true", so that I can see what's going on.
But now the screen is both flickering, for periods it's just white, and now and then I can see cells beeing updated on the screen - without seeing the rest of the sheet/cells...

I've tried setting hardware graphics acceleration in my excel to "disabled", but it didn't help.
Problem is the same on both my private pc and my job-pc. Both have Windows 10 and Excel 2016.

Any ideas on how this can be fixed?

 

[Joe4]

What happens if you set "ScreenUpdating = False"?
Does that stop the flickering (that is what that command is supposed to do)?
Test it out and see if that corrects that issue.

I prefer to have "screenupdating = true", so that I can see what's going on.

I think you may need to make a choice here. Either you want to see what is going on (screen flickering) or you don't.

 

[kit99]

What happens if you set "ScreenUpdating = False"?
Does that stop the flickering (that is what that command is supposed to do)?
Test it out and see if that corrects that issue.


I think you may need to make a choice here. Either you want to see what is going on (screen flickering) or you don't.

Just tried to set "ScreenUpdating = False" from top to bottom of my code. It's still not perfect. Now the screen pulses between showing my wb and a white screen.
And I don't understand why it can't be at "True"? It worked just perfect with that in Excel 2010...

 

[Joe4]

Care to post your code?

Perhaps changing calculations to manual until the end will help.

 

[kit99]

Care to post your code?

Perhaps changing calculations to manual until the end will help.

Well..., the code veeery long. But it's nothing special, it just open various wb's, retreiving whole sheets or cells of data, and pasting it into other wb's/sheets. Updates a few pivottables, and that's it.
But here's an example:

Sub PG_1_OppdatereALT()
 
    Call KIT_1_OppdatereALT_SumSection
 
    Call KIT_1_OppdatereALT_group1
    Call KIT_1_OppdatereALT_group2
    Call KIT_1_OppdatereALT_group3
    Call KIT_1_OppdatereALT_group4
    Call KIT_1_OppdatereALT_group5
    Call KIT_1_OppdatereALT_group6
               
End Sub
 
 
Private Sub KIT_1_OppdatereALT_SumSection()
 
Dim wb As Workbook, sh As Worksheet, fPath As String, fName As String
Dim pt As PivotTable
Dim ws As Worksheet
Dim pc As PivotCache
Dim pf As PivotField
Dim pi As PivotItem
fPath = ThisWorkbook.Path
If Right(fPath, 1) <> "\" Then fPath = fPath
 
 
Windows("01-Sum_SECTION_(M).xlsm").Activate
 
    Sheets("NYsum1").Visible = True
    Sheets("NYf1").Visible = True
    Sheets("NYf2").Visible = True
    Sheets("NYe").Visible = True
    Sheets("NYd").Visible = True
    Sheets("NYs").Visible = True
    Sheets("FOsum1").Visible = True
    Sheets("FOf1").Visible = True
    Sheets("FOf2").Visible = True
    Sheets("FOe").Visible = True
    Sheets("FOd").Visible = True
    Sheets("FOs").Visible = True
   
    Sheets("Data1").Visible = True
    Sheets("cn1").Visible = True
    Sheets("cn2").Visible = True
    Sheets("pr-sum1").Visible = True
    Sheets("pr-f1").Visible = True
    Sheets("pr-f2").Visible = True
    Sheets("pr-e").Visible = True
    Sheets("pr-s").Visible = True
   
    Sheets("piv-a").Visible = True
    Sheets("piv-b").Visible = True
    Sheets("piv-c").Visible = True
    Sheets("piv-d").Visible = True
    Sheets("piv-e").Visible = True
    Sheets("piv-f").Visible = True
    Sheets("piv-g").Visible = True
    Sheets("piv-h").Visible = True
    Sheets("piv-i").Visible = True
    Sheets("piv-j").Visible = True
    Sheets("piv-k").Visible = True
   
    Sheets("1-frv").Visible = True
    Sheets("2-frh").Visible = True
    Sheets("3-frk").Visible = True
    Sheets("4-reu").Visible = True
    Sheets("5-rem").Visible = True
    Sheets("6-sk").Visible = True
    Sheets("7-skb").Visible = True
    Sheets("8-oth").Visible = True
    Sheets("9-ohs").Visible = True
    Sheets("10-td").Visible = True
    Sheets("11-kmi").Visible = True
    Sheets("12-ile").Visible = True
 
 
        Set wb = Workbooks.Open(fPath & "\R2\" & "Wb1" & ".xls")
        wb.Sheets(1).UsedRange.Copy ThisWorkbook.Sheets("NYsum1").Range("A1")
        wb.Close False
        On Error GoTo 0
   
        Set wb = Workbooks.Open(fPath & "\R2\" & "Wb2" & ".xls")
        wb.Sheets(1).UsedRange.Copy ThisWorkbook.Sheets("NYf1").Range("A1")
        wb.Close False
        On Error GoTo 0
   
        Set wb = Workbooks.Open(fPath & "\R2\" & "Wb3" & ".xls")
        wb.Sheets(1).UsedRange.Copy ThisWorkbook.Sheets("NYf2").Range("A1")
        wb.Close False
        On Error GoTo 0
       
        Set wb = Workbooks.Open(fPath & "\R2\" & "Wb4" & ".xls")
        wb.Sheets(1).UsedRange.Copy ThisWorkbook.Sheets("NYe").Range("A1")
        wb.Close False
        On Error GoTo 0
   
        Set wb = Workbooks.Open(fPath & "\R2\" & "Wb5" & ".xls")
        wb.Sheets(1).UsedRange.Copy ThisWorkbook.Sheets("NYd").Range("A1")
        wb.Close False
        On Error GoTo 0
       
        Set wb = Workbooks.Open(fPath & "\R2\" & "Wb6" & ".xls")
        wb.Sheets(1).UsedRange.Copy ThisWorkbook.Sheets("NYs").Range("A1")
        wb.Close False
        On Error GoTo 0
       
                    
 
    ThisWorkbook.Sheets(«Kont»).Select
    foundrow = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Find(«SUM alle knt (kun F)», Cells(1, 1)).Row
    Range("G" & foundrow & ":EO" & foundrow).Copy
    ThisWorkbook.Sheets("Data1").Activate
    Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
               
                                                 
                                                 
   
    Set wb = Workbooks.Open(fPath & "\SC\" & "Wb-sc1" & ".xls")
    wb.Sheets(1).Activate
    Range("B4:B6").Copy
    Windows(«01-Sum_SECTION_(M).xlsm»).Activate
    Sheets("cn2").Select
    Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    wb.Close False
    Set wb = Workbooks.Open(fPath & "\SC\" & "Wb-sc1" & ".xls")
    wb.Sheets(1).Activate
    Range("C7:C7").Copy
    Windows(«01-Sum_SECTION_(M).xlsm»).Activate
    Sheets("cn2").Select
    Range("F" & Rows.Count).End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    wb.Close False
     
    Set wb = Workbooks.Open(fPath & "\SC\" & "Wb-sc2" & ".xls")
    wb.Sheets(1).Activate
    Range("B4:B6").Copy
    Windows(«01-Sum_SECTION_(M).xlsm»).Activate
    Sheets("cn2").Select
    Range("I" & Rows.Count).End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    wb.Close False
    Set wb = Workbooks.Open(fPath & "\SC\" & "Wb-sc2" & ".xls")
    wb.Sheets(1).Activate
    Range("C7:C7").Copy
    Windows(«01-Sum_SECTION_(M).xlsm»).Activate
    Sheets("cn2").Select
    Range("L" & Rows.Count).End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    wb.Close False
     
    Set wb = Workbooks.Open(fPath & "\SC\" & "Wb-sc3" & ".xls")
    wb.Sheets(1).Activate
    Range("B4:B6").Copy
    Windows(«01-Sum_SECTION_(M).xlsm»).Activate
    Sheets("cn2").Select
    Range("O" & Rows.Count).End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    wb.Close False
    Set wb = Workbooks.Open(fPath & "\SC\" & "Wb-sc3" & ".xls")
    wb.Sheets(1).Activate
    Range("C7:C7").Copy
    Windows(«01-Sum_SECTION_(M).xlsm»).Activate
    Sheets("cn2").Select
    Range("R" & Rows.Count).End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    wb.Close False
     
    Set wb = Workbooks.Open(fPath & "\SC\" & "Wb-sc4" & ".xls")
    wb.Sheets(1).Activate
    Range("B4:B6").Copy
    Windows(«01-Sum_SECTION_(M).xlsm»).Activate
    Sheets("cn2").Select
    Range("U" & Rows.Count).End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    wb.Close False
    Set wb = Workbooks.Open(fPath & "\SC\" & "Wb-sc4" & ".xls")
    wb.Sheets(1).Activate
    Range("C7:C7").Copy
    Windows(«01-Sum_SECTION_(M).xlsm»).Activate
    Sheets("cn2").Select
    Range("X" & Rows.Count).End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    wb.Close False
     
    Set wb = Workbooks.Open(fPath & "\SC\" & "Wb-sc5" & ".xls")
    wb.Sheets(1).Activate
    Range("B4:B6").Copy
    Windows(«01-Sum_SECTION_(M).xlsm»).Activate
    Sheets("cn2").Select
    Range("AA" & Rows.Count).End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    wb.Close False
    Set wb = Workbooks.Open(fPath & "\SC\" & "Wb-sc5" & ".xls")
    wb.Sheets(1).Activate
    Range("C7:C7").Copy
    Windows(«01-Sum_SECTION_(M).xlsm»).Activate
    Sheets("cn2").Select
    Range("AD" & Rows.Count).End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    wb.Close False
   
    Set wb = Workbooks.Open(fPath & "\SC\" & "Wb-sc6" & ".xls")
    wb.Sheets(1).Activate
    Range("B4:B6").Copy
    Windows(«01-Sum_SECTION_(M).xlsm»).Activate
    Sheets("cn2").Select
    Range("AG" & Rows.Count).End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    wb.Close False
    Set wb = Workbooks.Open(fPath & "\SC\" & "Wb-sc6" & ".xls")
    wb.Sheets(1).Activate
    Range("C7:C7").Copy
    Windows(«01-Sum_SECTION_(M).xlsm»).Activate
    Sheets("cn2").Select
    Range("AJ" & Rows.Count).End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    wb.Close False
   
    Set wb = Workbooks.Open(fPath & "\SC\" & "Wb-sc7" & ".xls")
    wb.Sheets(1).Activate
    Range("B4:B6").Copy
    Windows(«01-Sum_SECTION_(M).xlsm»).Activate
    Sheets("cn2").Select
    Range("AM" & Rows.Count).End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    wb.Close False
    Set wb = Workbooks.Open(fPath & "\SC\" & "Wb-sc7" & ".xls")
    wb.Sheets(1).Activate
    Range("C7:C7").Copy
    Windows(«01-Sum_SECTION_(M).xlsm»).Activate
    Sheets("cn2").Select
    Range("AP" & Rows.Count).End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    wb.Close False
   
   
             
Sheets("cn1").Select
Application.ScreenUpdating = False
 
    Sheets("cn1").Range("U3:U198").Value = _
    Sheets("cn2").Range("B3:B198").Value
        Sheets("cn1").Range("V3:V198").Value = _
        Sheets("cn2").Range("H3:H198").Value
    Sheets("cn1").Range("W3:W198").Value = _
    Sheets("cn2").Range("N3:N198").Value
        Sheets("cn1").Range("X3:X198").Value = _
        Sheets("cn2").Range("T3:T198").Value
    Sheets("cn1").Range("Y3:Y198").Value = _
    Sheets("cn2").Range("Z3:Z198").Value
        Sheets("cn1").Range("Z3:Z198").Value = _
        Sheets("cn2").Range("AF3:AF198").Value
    Sheets("cn1").Range("AA3:AA198").Value = _
    Sheets("cn2").Range("AL3:AL198").Value
  
        Sheets("cn1").Select
        For Each Cell In [U3:U198]
        If Cell.Value = "0" Then Cell.ClearContents
        Next Cell
 
    Sheets("cn1").Range("AC3:AC198").Value = _
    Sheets("cn2").Range("E3:E198").Value
        Sheets("cn1").Range("AD3:AD198").Value = _
        Sheets("cn2").Range("K3:K198").Value
    Sheets("cn1").Range("AE3:AE198").Value = _
    Sheets("cn2").Range("Q3:Q198").Value
        Sheets("cn1").Range("AF3:AF198").Value = _
        Sheets("cn2").Range("W3:W198").Value
    Sheets("cn1").Range("AG3:AG198").Value = _
    Sheets("cn2").Range("AC3:AC198").Value
        Sheets("cn1").Range("AH3:AH198").Value = _
        Sheets("cn2").Range("AI3:AI198").Value
    Sheets("cn1").Range("AI3:AI198").Value = _
    Sheets("cn2").Range("AO3:AO198").Value
 
        Sheets("cn1").Select
        For Each Cell In [AC3:AI198]
        If Cell.Value = "0" Then Cell.ClearContents
        Next Cell
      
    On Error GoTo ErrorHandler
    Sheets("cn1").Range("AS3:AY198").Value = _
    Sheets("cn1").Range("AK3:AQ198").Value
                       
        Sheets("cn1").Select
        For Each Cell In [AS3:AY198]
        If Cell.Value = "0" Then
        Cell.Value = Cell.Offset(-1, 0).Value
        End If
        Next Cell
 
Application.ScreenUpdating = True
       
 
 
                                                     
        Sheets(«Dash»).Select
                
            Application.ScreenUpdating = False
            Application.DisplayAlerts = False
            On Error Resume Next
           
            Windows(«01-Sum_SECTION_(M).xlsm»).Activate
 
Sheets("piv-a").Select
 
    For Each pt In Worksheets("piv-a").PivotTables
            pt.RefreshTable
                Next pt
 
                ActiveSheet.PivotTables("Pivottabell7").PivotFields(«Kon").AutoSort _
                xlAscending, "Sum1"
 
                ActiveSheet.PivotTables("Pivottabell8").PivotFields(«Kon").AutoSort _
                xlAscending, "Sum2"
       
                ActiveSheet.PivotTables("Pivottabell1").PivotFields(«Kon").AutoSort _
                xlAscending, "Sum3"
               
 
 
Sheets("piv-i").Select
 
    For Each pt In Worksheets("piv-a").PivotTables
            pt.RefreshTable
                Next pt
 
 
Sheets("piv-k").Select
 
    For Each pt In Worksheets("piv-k").PivotTables
            pt.RefreshTable
                Next pt
 
           
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
                                                                   
                                                                                                  
 
    Sheets("NYsum1").Visible = False
    Sheets("NYf1").Visible = False
    Sheets("NYf2").Visible = False
    Sheets("NYe").Visible = False
    Sheets("NYd").Visible = False
    Sheets("NYs").Visible = False
    Sheets("FOsum1").Visible = False
    Sheets("FOf1").Visible = False
    Sheets("FOf2").Visible = False
    Sheets("FOe").Visible = False
    Sheets("FOd").Visible = False
    Sheets("FOs").Visible = False
   
    Sheets("Data1").Visible = False
    Sheets("cn1").Visible = False
    Sheets("cn2").Visible = False
    Sheets("pr-sum1").Visible = False
    Sheets("pr-f1").Visible = False
    Sheets("pr-f2").Visible = False
    Sheets("pr-e").Visible = False
    Sheets("pr-s").Visible = False
   
    Sheets("piv-a").Visible = False
    Sheets("piv-b").Visible = False
    Sheets("piv-c").Visible = False
    Sheets("piv-d").Visible = False
    Sheets("piv-e").Visible = False
    Sheets("piv-f").Visible = False
    Sheets("piv-g").Visible = False
    Sheets("piv-h").Visible = False
    Sheets("piv-i").Visible = False
    Sheets("piv-j").Visible = False
    Sheets("piv-k").Visible = False
   
    Sheets("1-frv").Visible = False
    Sheets("2-frh").Visible = False
    Sheets("3-frk").Visible = False
    Sheets("4-reu").Visible = False
    Sheets("5-rem").Visible = False
    Sheets("6-sk").Visible = False
    Sheets("7-skb").Visible = False
    Sheets("8-oth").Visible = False
    Sheets("9-ohs").Visible = False
    Sheets("10-td").Visible = False
    Sheets("11-kmi").Visible = False
    Sheets("12-ile").Visible = False
   
 
   
        ThisWorkbook.Sheets(«Dash»).Select
        Range("A1").Select
        ActiveWorkbook.Save
 
Exit Sub
ErrorHandler:
           
           
End Sub
 
 
Private Sub KIT_1_OppdatereALT_group1()
 
… code is pretty much similar for each «private bit»…

 

[Joe4]

A few notes:

- One thing that causes screen flickering is unnecessary Select statements. Most of the time, if you have one line that ends in .Select or .Activate, and then next line begins with Selection or ActiveCell, those can usually be combined into one line, i.e. something like this:

    Range("J1").Select
    ActiveCell.FormulaR1C1 = "10"

can be combined to this"

    Range("J1") = "10"

Unnecessary Select statements with also slow your code down.

Also, I see that you are having your procedure call other procedures.
You may want to try to put the Application.ScreenUpdating = False line at the top of ALL of your procedures, just to make sure that it really is disabled for each one.

 

[kit99]

A few notes:

- One thing that causes screen flickering is unnecessary Select statements. Most of the time, if you have one line that ends in .Select or .Activate, and then next line begins with Selection or ActiveCell, those can usually be combined into one line, i.e. something like this:

    Range("J1").Select
    ActiveCell.FormulaR1C1 = "10"

can be combined to this"

    Range("J1") = "10"

Unnecessary Select statements with also slow your code down.

Also, I see that you are having your procedure call other procedures.
You may want to try to put the Application.ScreenUpdating = False line at the top of ALL of your procedures, just to make sure that it really is disabled for each one.

The first bit about select/activate is something I have to look into later. It will be quite a job changing this in all my code... Thanks for the tip!

But I just tried out setting "ScreenUpdating = False" at the very top (above "Call KIT_1_OppdatereALT_SumSection") and keeping it on "False" all the way to the bottom.
Screen still pulses between my wb and a white screen, and it seems to me that this happens every time my code opens up another wb in my code.
Is this supposed to happen even when everything is set to "False"? Apart from this, screen was calm running the other parts of the code.

When updating and copying so much data, I've seen the possibility of watching what my code is doing as a security. I've only put in "ScreenUpdating = False" on bits of code where speed otherwise would be too bad to keeping it at a practical level. So, even tho' I can end the flickering by changing my code, I still don't understand the basic about why this was working just fine in Excel 2007 and 2010, and not working at all in 2013 and 2016. Has something changed in Excel settings? Or is this a conscious change in the software made by Microsoft?

 

[Joe4]

Has something changed in Excel settings?

Possibly. And it might not be limited to just Office, but perhaps the Windows upgrade too.
I noticed how files open a little differently, as far as tabs and sessions go. So maybe something changed there with how files open, and it affects this.

Isn't it just more of an "inconvenience", than anything? I mean, the code still runs and does what it is supposed to, right?

 

[kit99]

Possibly. And it might not be limited to just Office, but perhaps the Windows upgrade too.
I noticed how files open a little differently, as far as tabs and sessions go. So maybe something changed there with how files open, and it affects this.

Isn't it just more of an "inconvenience", than anything? I mean, the code still runs and does what it is supposed to, right?

I usually does, yes. But it runs for around 10-15 minutes before ending every section/group I'm updating. It's quite a long time not knowing "how it's going"...
Is there a clever way of making my code let me know how far it's run? A message-box or something that can pop up and say "done with updating group 5"?

 

[Joe4]

Do a Google search on "Excel VBA Progress Bar", and see if maybe that is something you can incorporate.

If your code is taking that long to run, I would highly recommend trying to speed it up by incorporating using the things I told you above, as well as shutting off Automatic Calculations until the very end also.