Eliminating Screen Flickering in VBA Excel when Copying and Pasting Between Workbooks

Question

I've made a macro for copying certain data from a report workbook and pasting it into a summary workbook. Functionally, the macro works great, but I'm seeing a 'flickering' effect as the data is being moved between the workbooks. I have tried a number of tricks for eliminating it (see code), but it still flickers! Any suggestions on how to eliminate it, or what might be causing it?

I have referenced this similar question but it didn't work for my case.

Here is a slightly abbreviated version of my code. I think I've included all of the sections that might be related to this issue, but let me know if anything doesn't make sense.

Sub GetInfo()

'This macro copies and pastes certain information from a 
'report of a fixed format into a summary with a 'nicer' format.

'Variables
Dim xReport As Workbook
Dim xSummary As Workbook
Dim xReportSheet As Worksheet
Dim xSummarySheet As Worksheet
Dim rng As Range

'Initilizations
Set xSummary = Workbooks("Summary")
Set xSummarySheet = xSummary.ActiveSheet
Set xReport = Workbooks.Open(xFilePath)
Set xReportSheet = xReport.ActiveSheet

'Turn Off Window Flickering (but it doesn't work)
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False
Application.DisplayStatusBar = False
Application.DisplayAlerts = False


'Format info in each workbook.
With xSummary
    With xSummarySheet
        'Do some initial formatting to workbook prior to pasting info.
    End With
End With

With xReport
    With xReportSheet
        'Do some formatting on the info before copying.
    End With
End With


'Copy and Paste Data between workbooks.
    'Copy
    With xReport
        With xReportSheet
            Set rng = .Cells(2,5)
            Application.CutCopyMode = False
            rng.Copy
        End With
    End With

    'Paste
    With xSummary
        With xSummarySheet
            Set rng = .Cells(3,1)
            rng.PasteSpecial Paste:=xlpasteValues
        End With
    End With

    'Copy and Paste a few more times
    '...
    '...
    '...

'Return to normal
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
ActiveSheet.DisplayPageBreaks = True
Application.DisplayStatusBar = True
Application.DisplayAlerts = True

End Sub

Thank you

Answer 1

The double-nested With statements are not needed. You can only use 1 With...End With statement at a time (well, you can actually qualify a new With statement using a previous with statement, but you are not doing so in this case). Anyway, that isn't your issue.

Try seeing if avoiding copy/paste will work for what you need.

Replace all this:

'Copy and Paste Data between workbooks.
    'Copy
    With xReport
        With xReportSheet
            Set rng = .Cells(2,5)
            Application.CutCopyMode = False
            rng.Copy
        End With
    End With

    'Paste
    With xSummary
        With xSummarySheet
            Set rng = .Cells(3,1)
            rng.PasteSpecial Paste:=xlpasteValues
        End With
    End With

with this single line of code:

xSummarySheet.Cells(3, 1) = xReportSheet.Cells(2, 5).Value

If nothing else, I'm sure your code will at least run much faster.

Also, not sure if you are using .Activate or .Select. If you are, don't.