Runtime error 1004 : paste method of worksheet class failed

1
votes

There is lots of info on this error and how to fix but I've tried so many of them and nothing has worked: see below the problem chunk of code

Range("A1:K2").Select
Selection.Copy
Windows("EIL Depannage Register.xlsm").Activate
Sheets("Raising Depannage").Select
ActiveSheet.Unprotect
Sheets("Import_Sheet").Select
ActiveSheet.Unprotect
Range("A1").Select
Application.Wait (Now + TimeValue("00:00:10"))
ActiveSheet.Paste    <<<<<error happens at this line

Error occurring on the last line with .Paste.

4
vbaexcelruntime-error
You are likely getting tripped up because you're heavily using Select and Activate when you don't need to. Look at this, this, and this. - PeterT
Thanks for the tips - new to all this so appreciate the advice - LBishop

4 Answers

1
votes

The .Unprotect commands are cancelling your .CutCopyMode so there is essentially nothing to paste. You will have to unprotect the worksheets before initiating a Copy and Paste operation.

Dim ws As Worksheet
Set ws = ActiveSheet

Windows("EIL Depannage Register.xlsm").Activate
With ActiveWorkbook
    .Worksheets("Raising Depannage").Unprotect
    With .Worksheets("Import_Sheet")
        .Unprotect
        ws.Range("A1:K2").Copy Destination:=.Range("A1")
    End With
End With

I don't like using ActiveSheet to qualify the original worksheet but your code did not specify the workbook or worksheet and that left me little choice.

0
votes

Can you try it like this?

Range("A1:K2").Copy
Windows("EIL Depannage Register.xlsm").Activate
Sheets("Raising Depannage").Unprotect
Sheets("Import_Sheet").Unprotect
Sheets("Import_Sheet").Range("A1").Paste

Edit: How about like this:

Dim wb As Workbook
Dim ws As Worksheet

set wb = activeworkbook
set ws = activesheet

Windows("EIL Depannage Register.xlsm").Activate
Sheets("Raising Depannage").Unprotect
Sheets("Import_Sheet").Unprotect

wb.ws.Range("A1:K2").Copy
Windows("EIL Depannage Register.xlsm").Activate
Sheets("Import_Sheet").Range("A1").Paste

Play a bit, it should work.

0
votes

try

dim shSrc as worksheet
dim shTarget as worksheet
set shSrc =Workbooks("EIL Depannage Register.xlsm").Sheets("Raising Depannage")
set shTarget =Workbooks("EIL Depannage Register.xlsm").Sheets("Raising Depannage")
shSrc.unprotect
shTarget.unprotect
shSrc.Range("A1:K2").Copy
shTarget. Range("A1").Paste   
shSrc.protect
shTarget.protect
set shsrc=nothing
set shtarget=nothing`

just fix the source and target and verify the ranges

0
votes

The last line of the code should be:

Sheets("Import_Sheet").paste Range("A1")

as paste is a method of worksheet, and range("A1") is the destination argument.