Run-time error '1004': Paste method of Worksheet class failed

[OutOfIdeas]

Hi All

I have a problem with a piece of code that appeared to be working fine for the last 3 months but now throws a "Run-time error '1004': Paste method of Worksheet class failed" error.

The idea is that a user will copy a number of columns from another workbook to the clipboard and will then click the button on my workbook, which will copy the data in and carry out a couple of other tasks too (which I've removed for simplicity). However, just recently the action has failed at the line marked below. Here's my code:

Private Sub cmdPasteAndSort_Click()
    Dim intCurAcc, intCurLine As Long
    
    Dim strCurAcc, strCurLine As String
        
    
    x = MsgBox("Before you proceed please ensure that you have run the Datafile 'Sales by Product Code/Customer' report for the correct period, exported to Excel and copied columns A through L to the clipboard. Then click OK.", vbOKOnly, "Before continuing...")
    
    'Clear columns A to L
    Worksheets("Data").Columns("A:L").ClearContents
    
    'Paste clipboard into A1
    Application.CutCopyMode = False
    Worksheets("Data").Range("A1").Select
    ActiveSheet.Paste '<---fails here---
End Sub

I've trawled MrExcel (a valued source of many a solution in the past) and, although I can find plenty of solutions to other people having this problem, none seem to fix mine.

During troubleshooting I have established that even if I copy a single cell from my workbook and click the button it still fails... BUT if I put simple text on the clipboard it succeeds. Can anyone help? I've been at this for 3 hours now

Thanks in advance.

 

[AlphaFrog]

As a test, comment out this line and give it a try.

[COLOR="Green"]'Application.CutCopyMode = False[/COLOR]

Otherwise, can you show the code where you are making the copy?

 

[OutOfIdeas]

Thanks AlfaFrog.
I had the same idea. I have tried with this line commented out and the problem persists. However, I have found that in addition if I also comment out :

Worksheets("Data").Columns("A:L").ClearContents

...then the paste works. Could it be that the ClearContents function removes the range of cells that have been put on the clipboard (as happens when you copy a range but then modify another cell before pasting)? If so, do you know how you can make functions not affect the contents of Excels clipboard?

FYI: there is no code to copy the cells as this is done manually by the user.

I appriciate your response. Thanks for taking the time.

 

[OutOfIdeas]

Hi

Just to let you know that I have found that the "ClearContents" function does change the "CutCopyMode" to False. I have modified my code to paste the selected cells to a temporary location before the "ClearContents" funciton is called, as below:

Private Sub cmdPasteAndSort_Click()
    Dim intCurAcc, intCurLine As Long
    
    Dim strCurAcc, strCurLine As String
        
    
    x = MsgBox("Before you proceed please ensure that you have run the Datafile 'Sales by Product Code/Customer' report for the correct period, exported to Excel and copied columns A through L to the clipboard. Then click OK.", vbOKOnly, "Before continuing...")
    
    'Paste to Temp Location
    Worksheets("Data").Range("P1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    'Clear columns A to L
    Worksheets("Data").Columns("A:L").ClearContents
    
    'Copy P to AA and Paste clipboard into A1
    Worksheets("Data").Range("P:AA").Copy
    Worksheets("Data").Range("A1").Select
    ActiveSheet.Paste
    Worksheets("Data").Columns("P:AA").ClearContents
End Sub

It is worth noting that this function did not clear the clipboard contents in previous attempts. This code has been run weekly for the last three months without failure. Either I have changed something with my configuration that I am not aware of or there has been a change through Microsoft Update that has altered this function. However, I can find no evidence of either.

Nonetheles, thanks to all who read my threat and to AlphaFrog for his reply.

 

[AlphaFrog]

Alternatively, instead of immediately copying the selection, set the selection to a range variable. Then clear and then copy\paste the variable.

Example:

Private Sub cmdPasteAndSort_Click()
    Dim intCurAcc, intCurLine As Long
    Dim strCurAcc, strCurLine As String
    [COLOR="Red"]Dim rng As Range[/COLOR]
    
    x = MsgBox("Before you proceed please ensure that you have run the Datafile 'Sales by Product Code/Customer' report for the correct period, exported to Excel and copied columns A through L to the clipboard. Then click OK.", vbOKOnly, "Before continuing...")

    [COLOR="red"]Set rng = Selection[/COLOR]
    
    'Clear columns A to L
    Worksheets("Data").Columns("A:L").ClearContents
    
    [COLOR="red"]rng.Copy Destination:=Worksheets("Data").Range("A1")[/COLOR]
    
End Sub

Or just copy the selection after you clear.

Private Sub cmdPasteAndSort_Click()
    Dim intCurAcc, intCurLine As Long
    Dim strCurAcc, strCurLine As String
    
    x = MsgBox("Before you proceed please ensure that you have run the Datafile 'Sales by Product Code/Customer' report for the correct period, exported to Excel and copied columns A through L to the clipboard. Then click OK.", vbOKOnly, "Before continuing...")
   
    'Clear columns A to L
    Worksheets("Data").Columns("A:L").ClearContents
    
    [COLOR="Red"]Selection[/COLOR].Copy Destination:=Worksheets("Data").Range("A1")
    
End Sub