View original (French)

Error, the PasteSpecial method of the Range class failed.

Solved
JoeLaBricole -  
 JoeLaBricole -
Hello everyone,

The macro below is used to copy a list created by the user by selecting a variable number of products. This list is in column B of the Recap sheet. Once copied, this list is pasted into the General sheet starting from cell N2.

This macro fails on the paste line, when it reaches: ".Range("N2")....." the macro gives the following message: Error, The PasteSpecial method of the Range class has failed

What troubles me is that if I move the macro's indicator to: " With Worksheets("Recap") " and press F5, the macro runs without any problems.

So, who can help me?
PS: I also tried with: ActiveSheet.Paste, but the problem is the same. 

 'Reporter With Worksheets("Recap") X = 8 While .Range("B" & X) <> "" X = X + 1 Wend X = X - 1 .Range("B8", "B" & X).Copy With Worksheets("Général") .Activate .Unprotect .Range("N2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False .Protect End With Sheets("Recap").Activate End With


Thank you in advance, and I send you my best regards.

1 answer

  1.
    michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    Hello

    try 
    With Worksheets("Recap")
     X = .Columns("B").Find("*", , , , , xlPrevious).Row
     buffer = .Range("B8", "B" & X)
     End With
     With Worksheets("General")
     .Unprotect
     .Range("N2:N" & X - 6) = buffer
     .Protect
     End With

    End Sub


    Michel
    0
    1. JoeLaBricole
       
      Great,

      A big thank you to you michel_m

      Have a nice day!
      0