时间戳

首页 > 解决方案 > 在工作簿之间复制和粘贴时消除 VBA Excel 中的屏幕闪烁

excel - 在工作簿之间复制和粘贴时消除 VBA Excel 中的屏幕闪烁

问题描述

我制作了一个宏，用于从报告工作簿中复制某些数据并将其粘贴到摘要工作簿中。从功能上讲，宏工作得很好，但是当数据在工作簿之间移动时，我看到了“闪烁”的效果。我尝试了许多消除它的技巧（见代码），但它仍然闪烁！关于如何消除它或可能导致它的任何建议？

我已经引用了这个类似的问题，但它不适用于我的情况。

这是我的代码的略微缩写版本。我想我已经包含了所有可能与此问题相关的部分，但如果有任何不妥之处，请告诉我。

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

谢谢

标签： excelvbawindowcopy-pasteflicker

解决方案

With不需要双嵌套语句。您一次只能使用 1 个With...End With语句（好吧，您实际上可以使用以前的 with 语句来限定新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

使用这一行代码：

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

如果不出意外，我相信您的代码至少会运行得更快。

另外，不确定您是否使用.Activateor .Select。如果你是，不要。

推荐阅读