Anyway to stop Excel from flickering when opening a macro

syndee

New Member
Joined
Jan 14, 2017
Messages
18
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.
Code:
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
 
Last edited by a moderator:

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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?
 
Last edited:
Upvote 0
No, there is only code in this workbook. Not in any it opens. They are csv files.
 
Upvote 0
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
 
Upvote 0
Do I put this at the start and end of code or with each of the 3 csv files that opens in the code?
 
Upvote 0
Hi Syndee.
Try this:
Rich (BB code):
' 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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

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
Back
Top