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
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