Notification of When a Copy is Complete

David notes that when copying multiple columns (say, 10) by several hundred thousand rows, it understandably takes a while for Excel to pull it all into the Clipboard. He usually gets flashes of data on the screen, and the occasional "not responding" message, but it usually will copy without crashing. When he then tries to paste after 10 to 20 seconds, he finds that Excel's not finished copying. David wonders if there is a way to KNOW when the copy is completed so he knows it is safe to paste. A message such as "copy complete" would be helpful in these situations.

When you use Ctrl+C to copy a huge amount of data, when the copy is complete, the status bar should display this: "Select destination and press ENTER or choose Paste." Of course, if you wait long enough, the message will disappear and you may be left to wonder whether the copy has completed or not.

You can, however, do the copy using a macro, and the macro can display a message when the copy is complete. Here is a very simple example:

Sub ReportCopy()
    Selection.Copy
    MsgBox "copy complete"
End Sub

Make your selection, run the macro, and when the copy has been made, then the information is within the Clipboard, ready to be pasted.

When using any copying that involves the Clipboard, you need to understand that the process is inherently slow, particularly if you don't have a lot of memory within your PC. If what you are copying requires more memory than what is available on your system, then Windows uses virtual memory, which means it writes some of the information to your drive's cache. This slows things down even more.

You can, if desired, do both the copy and paste within a macro, which bypasses the Clipboard completely. Here's an example of such an approach:

Sub Rnge_Copy()
    Dim LeftEdge As Long
    Dim RightEdge As Long
    Dim TopEdge As Long
    Dim BottomEdge As Long
    Dim DestRnge As Range

    TopEdge = ActiveCell.Row
    LeftEdge = ActiveCell.Column
    BottomEdge = TopEdge + Selection.Rows.Count - 1
    RightEdge = LeftEdge + Selection.Columns.Count - 1

    On Error Resume Next
    Set DestRnge = InputBox("Enter the top left destination cell address", Type:=8)
    If Err.Number = 0 Then
        Range(Cells(TopEdge, LeftEdge), Cells(BottomEdge, RightEdge)).Copy _
          Destination:=DestRnge
    End If
    On Error GoTo 0
End Sub

To use the macro, select the range you want to copy and then run it. You are asked to provide an address for the upper-left corner of the destination. (The macro copies information within the same worksheet, not between worksheets or workbooks.) The error handling in the macro is in place in case the user provides an invalid destination address or clicks on the Cancel button.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11566) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.