How to prevent flickering / switching windows in Excel?

Question

I copy from one file to another file and see how Excel switches between the source and destination files (for example, flicker). I want the macro to be copied from the source and pasted into the target without switching between files (I don't want to flicker).

So, I got my Excel VBA code. I set a button to run 2 macros. The first macro is openfiledialog, where I can select the file to open. This is necessary because the desired file always received a different name and was in a different path.

After opening the file, my second macro (CopyPasteValues) is launched, which I posted below. First, I set the source and target books and worksheets and create an array with 16 account numbers.

Then I use the find method to find the account number in each file (source and target). The result of the find method is used to create an offset in the src file and copy it to the offset in the target file.

Sub CopyPasteValues()
    Dim srcWb As Workbook    'source Wb
    Dim srcWs As Worksheet   'source Ws
    Dim trgWb As Workbook    'target Wb
    Dim trgWs As Worksheet   'target Ws

    Set trgWb = ActiveWorkbook
    Set trgWs = trgWb.Sheets("Entry Sheet 20004100")
    Set srcWb = Workbooks.Open(Filename:=openedFile, UpdateLinks:=False, ReadOnly:=True, Editable:=False)
    Set srcWs = srcWb.Sheets("20004100") 

    Dim GLAccountField
    'Array of 16 Account numbers
    GLAccountField = Array(430000, 446030, 477030, 474210, 446075, 472700, 472710, 476000, 476100, 476610, 452200, 454700, 471300, 473110, 490000, 490710)

    Dim srcFinder As Range, trgFinder As Range
    Dim searchGL As Long
    Dim srcRng As Range, trgRng As Range
    Dim i As Integer

    For i = LBound(GLAccountField) To UBound(GLAccountField)

        'The range where GL Accounts will be searched
        Set srcRng = srcWs.Range("A1:A100")    'source file
        Set trgRng = trgWs.Range("B10:B900")   'target file

        'search for the account number(i) in source and target sheets
        searchGL = GLAccountField(i) 
        Set srcFinder = srcRng.Find(searchGL, Lookat:=xlWhole, LookIn:=xlValues, MatchCase:=True)
        Set trgFinder = trgRng.Find(searchGL, Lookat:=xlWhole, LookIn:=xlValues, MatchCase:=True)


        'If finder value equals searched Account Number, then paste to target
        If srcFinder Is Nothing Then
            MsgBox "GL Account: " & searchGL & " NOT found in 'Accounting Input' file"
        Else
            'copy from source
            srcFinder.Offset(0, 15).Resize(1, 12).Copy
            'paste to target from source
            trgFinder.Offset(1, 4).Resize(1, 12).PasteSpecial xlPasteValues  

        End If
    Next i
    srcWb.Close
End Sub

Answer 1

(Answered in Andy G's comments):

Use Application.ScreenUpdating = Falseat the beginning of your sub. Remember to set it back at Truethe end (it is also good practice to do this in the error handler so that it gets reset even in case of an error):

Sub foo()
  On Error Goto errHandler
  Application.ScreenUpdating = False

  'Your code here

  Application.ScreenUpdating = True
errHandler:
  Application.ScreenUpdating = True
End Sub

Answer 2

during timer macro run ... Icons flicker, how to avoid ... please help me ..

Sub Starttimer ()

Sheets (""). Application.OnTime Now + TimeValue ("00:00:01"), "nexttick" End Sub

Sub Stoptimer() Sheets ("Screen"). . Time Now + TimeValue ("00:00:01"), "nexttick", False End Sub

Sub Nexttick() Sheets ("Screen"). If Worksheets ("screen"). Range ("H1") = 0 Application.Run "cfe_Part4.xlsm! Dodo" Application.Run "CFE_Part4.xlsm! Starttimer" ("screen"). Range ("H1"). Value = Worksheets ("screen"). Range ("h1"). Value - TimeValue ("00:00:01") End Sub