locked
How do I stop the screen from flashing as a table is being created using multiple copy paste RRS feed

  • Question

  • Hi; My code is evaluating a table on one sheet and if the record meets the criteria it is copy and pasted into a target worksheet. While it runs the screen is flashing as it updated each line. I thought I saw that you could disable this screen refresh until the program was complete. Can anyone tell me how this would be done? Below is a section fo the code which is performaing this task. Thanks in advance!


    SourceWS = "DASHBOARDSTG"                                                                               'Set Source Worksheet Name
    TargetWS = "STG Exceptions Sum"                                                                         'Set Target Worksheet Name
    SourceRw = 45                                                                                           'Set Start Source Row
    TargetRw = 5                                                                                            'Set First Target Row

    With Worksheets(TargetWS).Range("A5:W50")                                                               ' Clear Target Rows
            .ClearContents
            .ClearFormats
    End With
       
        Do Until SourceRw = 98                                                                               'Check if exception "3" and if so Cut and paste
            Worksheets(SourceWS).Activate
            If Worksheets(SourceWS).Cells(SourceRw, 2).Value = 3 Then
                Worksheets(SourceWS).Range(Cells(SourceRw, 2), Cells(SourceRw, 11)).Copy
                Worksheets(TargetWS).Activate
                Worksheets(TargetWS).Range(Cells(TargetRw, 1), Cells(TargetRw, 10)).PasteSpecial Paste:=xlValues
                Worksheets(TargetWS).Range(Cells(TargetRw, 1), Cells(TargetRw, 10)).PasteSpecial Paste:=xlFormats
                TargetRw = TargetRw + 1
            End If
        SourceRw = SourceRw + 1
        Loop

    Tuesday, September 1, 2009 7:56 PM

Answers

  • See if you can generate an array of data and then copy that entire to an array of cells in a single attempt. That way worksheet will flash only once.
    • Proposed as answer by Crazypennie Wednesday, September 2, 2009 1:20 AM
    • Marked as answer by Martin Xie - MSFT Tuesday, September 8, 2009 8:56 AM
    Tuesday, September 1, 2009 9:11 PM