Anyway to stop Excel from flickering when opening a macro

[syndee]

Have workbook that does everything I want but flickers when I open it. I have Screen updating. false but still flickers. Maybe it's normal, just thought I'd ask.

Private Sub Workbook_Open()
Application.ScreenUpdating = False


Workbooks.Open (ThisWorkbook.Path & "\Accrual Report.csv")
Range("A1:I10000").Select
    Selection.Copy
    Range("A1").Select
    Windows("Control File.xlsm").Activate
    Sheets("Accrual").Select
    Range("A1:I10000").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False
        Range("A1").Select
    
Workbooks.Open (ThisWorkbook.Path & "\Canada Validation Report.csv")
Range("A1:E2000").Select
    Selection.Copy
    Range("A1").Select
    Windows("Control File.xlsm").Activate
    Sheets("Canada Validation").Select
    Range("A1:E2000").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False
        Range("A1").Select


Workbooks("Accrual Report.csv").Close
Application.DisplayAlerts = False
Workbooks("Canada Validation Report.csv").Close
Application.DisplayAlerts = False


 Sheets("Export").Select
 Range("A1:I1").Select
    Selection.AutoFilter
    Range("A1").Select
    ActiveSheet.Range("$A$1:$I$2000").AutoFilter Field:=1, Criteria1:="<>0", _
        Operator:=xlAnd
    Range("A1:I2001").Select
    Selection.Copy
    Range("A1").Select
    Workbooks.Add
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveWorkbook.SaveAs Filename:= _
        (ThisWorkbook.Path & "\Time Off Import.csv"), _
        FileFormat:=xlCSV, CreateBackup:=False
    ActiveWindow.Close
    


    Application.ScreenUpdating = True
Windows("Control File.xlsm").Activate
    Sheets("Export").Select
End Sub

 

[gallen]

Is there code that runs when these workbooks open?

 

[syndee]

The above runs when workbook opens. Not sure what you mean by code,

 

[gallen]

The code runs when the workbook opens, but the code opens other workbooks. Is there code in those workbooks that runs when they open that may re-enable screen updating?

 

[syndee]

No, there is only code in this workbook. Not in any it opens. They are csv files.

 

[Zenwood]

I had a similar problem with a macro and fixed it by using Application.WindowState instead of Application.ScreenUpdating.

Application.WindowState = xlMinimized
Application.WindowState = xlMaximized

 

[syndee]

Do I put this at the start and end of code or with each of the 3 csv files that opens in the code?

 

[ZVI]

Hi Syndee.
Try this:

' Code of ThisWorkbook module
Option Explicit
 
Private Sub Workbook_Open()
  Application.OnTime Now, "'" & Me.Name & "'!" & Me.CodeName & ".MyMacro"
End Sub
 
Private Sub MyMacro()
 
  Application.ScreenUpdating = False
 
  With Workbooks.Open(ThisWorkbook.Path & "\Accrual Report.csv", ReadOnly:=True)
    .Sheets(1).Range("A1:I10000").Copy
    Workbooks("Control File.xlsm").Sheets("Accrual").Range("A1").PasteSpecial _
                      Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True
    Application.CutCopyMode = False
    .Close False
  End With
 
  With Workbooks.Open(ThisWorkbook.Path & "\Canada Validation Report.csv", ReadOnly:=True)
    .Sheets(1).Range("A1:E2000").Copy
    Workbooks("Control File.xlsm").Sheets("Canada Validation").Range("A1").PasteSpecial _
                        Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True
    Application.CutCopyMode = False
    .Close False
  End With
 
  With Workbooks("Control File.xlsm").Sheets("Export")
    If .AutoFilterMode Then .AutoFilterMode = False
    .Range("A1:I2000").AutoFilter Field:=1, Criteria1:="<>0", Operator:=xlAnd
    .Range("A1:I2001").Copy
  End With
  With Workbooks.Add(xlWBATWorksheet)
    .Sheets(1).Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=True
    Application.CutCopyMode = False
    .SaveAs Filename:=ThisWorkbook.Path & "\Time Off Import.csv", FileFormat:=xlCSV, CreateBackup:=False
    .Close False
  End With
 
  Workbooks("Control File.xlsm").Activate
  Sheets("Export").Select
  Range("A1").Select
 
  Application.ScreenUpdating = True
 
End Sub

Regards

 

[syndee]

Works perfectly. Thanks.

 

[ZVI]

Happy to help, Syndee!