Seeking method to disable Paste until button is clicked

[auto.pilot]

I have an error prone process whereby users copy a range of data from one workbook (source) and paste values it into another workbook (destination). There are a couple hundred different source and destination workbooks. As an example, errors occur when the existing destination workbook includes 1,200 rows and the source book data has 1,000 lines. Users are instructed to click a button which clears out the destination workbook, but this step is sometimes skipped. As such, 200 line items at the bottom of the data will remain in place, but should have been cleared out. I am seeking a method to disable the Paste function until the Clear button is clicked.

How can I do this?

Thanks in advance.

 

[ekrause]

I'd like to know more about the pasting process. Is this macro defined or is this a manual process? Furthermore would be comfortable writing/maintaining a macro?

Can be done pretty easily using VBA and will probably save a ton of time & headache in the long run.

But to answer your question, I don't know of a way to disable the paste function.

 

[auto.pilot]

Thanks. I found this bit of code via search and will likely adapt it for my use. The range will need to be specified separately for each destination book.
I'm considering running it upon open. I'm not a huge fan of this idea, but it's all I have at the moment.

Sub TestIsEmpty()
    If WorksheetFunction.CountA(Range("A6:AG1000")) = 0 Then
        MsgBox "Empty"
    Else
        MsgBox "Not Empty, Clear Previous Data"
    End If
End Sub

 

[ekrause]

It depends on how your workbooks are set up but there could be easier ways to accomplish the same thing across all books. If there is nothing below any of the tables in any of the workbooks, you can delete excess cells. E.g. If WorksheetFunction.CountA(Range("A6:AG100000")) = 0 Then

Or if you do have other calculations below those ranges and your data is set up to have no blank rows you can use a If WorksheetFunction.CountA(Range(Range("A6"), Range("A6").End(xlDown))) = 0 Then. This is equivalent to holding Ctrl+Shift+Down Arrow from A6.

Let me know if those help at all