VBA Copy/Paste Alternatives - Charts Flash/Flicker-CodePudding

Would like to try another way to copy/paste data from one worksheet to another. I've read the .copy and .paste script is inefficient and slow. I believe this is why my charts in another worksheet keep flickering/flashing to the point of not being able to read them.

The script below is triggered every couple milliseconds due to the Worksheet Calculate event and then copying from my dashboard to the log and incrementing to the next row every time the event is triggered for another copy/paste iteration. When the script is finished running it could have up to 60,000 rows of data copied over from dashboard. Script works great just the chart flashing makes it hard to interpret.

Private Sub Worksheet_Calculate()

If Worksheets("Dashboard").ToggleButton1.Value = True Then

On Error GoTo SafeExit
Application.EnableEvents = False
Application.ScreenUpdating = False 

    Set sht1 = ThisWorkbook.Sheets("Dashboard")
    Set sht2 = ThisWorkbook.Sheets("Log")
    Set cpyRng = sht1.Range("A3:M3")
    Set rngLogTargetBeginningCell = sht2.Cells(Rows.Count, 1).End(xlUp)
    Set rngLastCellSelection = Selection

    cpyRng.Copy
    rngLogTargetBeginningCell.Offset(1, 0).PasteSpecial xlPasteValues
    Application.CutCopyMode = False 
    rngLastCellSelection.Select
    
End If

SafeExit:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

CodePudding user response：

How about an alternate approach which doesn't use the clipboard?

Private Sub Worksheet_Calculate()

    If Not Worksheets("Dashboard").ToggleButton1.Value Then Exit Sub

    On Error GoTo SafeExit
    Application.EnableEvents = False
    Application.ScreenUpdating = False

    With ThisWorkbook.Sheets("Dashboard").Range("A3:M3")
        ThisWorkbook.Sheets("Log").Cells(Rows.Count, 1).End(xlUp). _
               Offset(1).Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With
    
SafeExit:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

CodePudding user response：

This is how I usually do, and for me works fine:

Private Sub Worksheet_Calculate()

    If Worksheets("Dashboard").ToggleButton1.Value = True Then
    
    On Error GoTo SafeExit
    
    Application.EnableEvents = False
    Application.ScreenUpdating = False 
        Sheets("Dashboard").UsedRange.Copy Sheets("Log").Range("A3") 'Informe where starts the data you want to copy
        Sheets("Log").UsedRange.Copy
        Sheets("Log").UsedRange.PastSpecial xlPasteValues
        Application.CutCopyMode = False
    
    End If
    
    SafeExit:
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End Sub