Where to place Application.ScreenUpdating function to stop flicker

[redspanna]

No matter where I put the Application.Screenupdating function in this code, it still flickers when running,

Where should it be placed to avoid the 'flicker'

Sub CopyEbayData()

   
    Dim sourceWorkbook As Workbook
    Dim targetWorkbook As Workbook
    Dim sourceWorksheet As Worksheet
    Dim copiedWorksheet As Worksheet
   
   Application.ScreenUpdating = False
   
    ' Set the source workbook (workbook with "ebay-orders" in its name)
    Set sourceWorkbook = GetWorkbookWithMatchingName("ebay-Listings")
    
    ' Check if the source workbook was found
    If Not sourceWorkbook Is Nothing Then
        ' Set the source worksheet (the only worksheet in the source workbook)
        Set sourceWorksheet = sourceWorkbook.Worksheets(1)
        
        ' Set the target workbook (workbook named "Sales4")
        Set targetWorkbook = ThisWorkbook ' Assuming this code is in the Sales4 workbook
        
        ' Copy the source worksheet to the target workbook
        sourceWorksheet.Copy After:=targetWorkbook.Sheets(targetWorkbook.Sheets.Count)
        
        ' Set the copied worksheet object reference
        Set copiedWorksheet = targetWorkbook.Sheets(targetWorkbook.Sheets.Count)
        
        ' Rename the copied worksheet to "ebay"
        copiedWorksheet.Name = "ebay"
        
         
        ' Clean up
        Set sourceWorksheet = Nothing
        sourceWorkbook.Close False ' Close the source workbook without saving changes
        Set sourceWorkbook = Nothing
        
        'run code to add VLOOKUP formula
        'If_string_match_found_place_formula
        'run code to change neagative numbers to positive
        'ChangeNegativeValues
        
  Application.ScreenUpdating = True
        
    Else
        MsgBox "Source workbook not found."
        
       
    End If
    
End Sub

Thanks in advance

 

[rlv01]

I have found that when opening and closing workbooks, sometimes you cannot suppress a brief flicker, even by turning off screen updating. I have classified it as one of those Microsoft things that I have no control over.

 

[redspanna]

I have found that when opening and closing workbooks, sometimes you cannot suppress a brief flicker, even by turning off screen updating. I have classified it as one of those Microsoft things that I have no control over.

It isn't a brief flicker, the updates last for a few seconds which I really don't want to have to see
any ideas where to insert the Application.screenupdating to stop it?

 

[Herakles]

It isn't a brief flicker, the updates last for a few seconds which I really don't want to have to see
any ideas where to insert the Application.screenupdating to stop it?

You have them either side of the code that is performing actions that are causing the flicker so that is as good as you can get.

What we don't know is what is happening in the GetWorkbookWithMatchingName function.

 

[redspanna]

Function GetWorkbookWithMatchingName(ByVal partialName As String) As Workbook

  Application.ScreenUpdating = False
    Dim wb As Workbook
    
    For Each wb In Workbooks
        If InStr(1, wb.Name, partialName, vbTextCompare) = 1 Then
            ' Found a workbook with a matching name
            Set GetWorkbookWithMatchingName = wb
            Exit Function
        End If
    Next wb
    
    ' No matching workbook found
    Set GetWorkbookWithMatchingName = Nothing
  Application.ScreenUpdating = True
End Function

 

[Herakles]

Does the last line ever get run?

You don't need the Application.ScreenUpdating lines in this function.

 

[redspanna]

Does the last line ever get run?

You don't need the Application.ScreenUpdating lines in this function.

sorry- which last line?

 

[Herakles]

sorry- which last line?

Application.ScreenUpdating = True

 

[redspanna]

so in this GetWorkbookWithMatchingName function code you are saying I should remove the Application.ScreenUpdating = False and Application.ScreenUpdating = True parts of the code?

 

[Herakles]

so in this GetWorkbookWithMatchingName function code you are saying I should remove the Application.ScreenUpdating = False and Application.ScreenUpdating = True parts of the code?

so in this GetWorkbookWithMatchingName function code you are saying I should remove the Application.ScreenUpdating = False and Application.ScreenUpdating = True parts of the code?

Yes, and move the Application.ScreenUpdating = True to the end of the CopyEbayData procedure.

I'm not saying that it will solve the problem though.