"Paste method of Worksheet class failed" but only on alternate executions

[sparky2205]

Hi folks,
can anyone tell me why this works every alternate time it's run?
Run it and it works fine.
Run it again "Paste method of Worksheet class failed"
Run it again it works fine.
Run it again "Paste method of Worksheet class failed"
etc.....
Thanks.

Sub CopyRows()

Dim RowNum As Integer
Dim pw As String
Dim LastRow As Long
LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
pw = "Test"
    Rows(LastRow & ":" & LastRow - 1).Activate
    Selection.Copy
    Rows(LastRow + 2).Select
    ActiveSheet.Unprotect Password:=pw
    Application.CutCopyMode = xlCopy
    ActiveSheet.Paste
    ActiveSheet.Range("B" & LastRow + 2 & ":" & "K" & LastRow + 2).ClearContents
    ActiveSheet.Range("B" & LastRow + 1).Locked = False
    
    ActiveSheet.Protect Password:=pw & _
                        AllowFormattingCells & _
                        AllowFormattingRows & _
                        AllowFormattingColumns
End Sub

 

[smozgur]

Try to unprotect the worksheet before Selection.Copy method.

'...
    ActiveSheet.Unprotect Password:=pw
    Selection.Copy
    Rows(LastRow + 2).Select
'...

 

[DanteAmor]

Move this line to the end of the code

[COLOR=#333333]Application.CutCopyMode = xlCopy[/COLOR]

Sub CopyRows()


Dim RowNum As Integer
Dim pw As String
Dim LastRow As Long
LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
pw = "Test"
    Rows(LastRow & ":" & LastRow - 1).Activate
    Selection.Copy
    Rows(LastRow + 2).Select
    ActiveSheet.Unprotect Password:=pw
    
    ActiveSheet.Paste
    ActiveSheet.Range("B" & LastRow + 2 & ":" & "K" & LastRow + 2).ClearContents
    ActiveSheet.Range("B" & LastRow + 1).Locked = False
    
    ActiveSheet.Protect Password:=pw & _
                        AllowFormattingCells & _
                        AllowFormattingRows & _
                        AllowFormattingColumns
[COLOR=#ff0000]    Application.CutCopyMode = xlCopy[/COLOR]
End Sub

You are copying the lines, but that instruction removes the contents of the memory. That's why when you try to paste it sends the error, because the memory is empty.

 

[sparky2205]

An interesting one guys.
Smozgur,
when I tried your solution I got the error each time I executed the code instead of alternately as before.
DanteAmor,
when I tried your solution it made no difference, I got the error alternately, as before.
However, then I tried both of your solutions together and now it works fine.
So, thank you both, and have a nice day.

 

[smozgur]

An interesting one guys.
Smozgur,
when I tried your solution I got the error each time I executed the code instead of alternately as before.
DanteAmor,
when I tried your solution it made no difference, I got the error alternately, as before.
However, then I tried both of your solutions together and now it works fine.
So, thank you both, and have a nice day.

It is interesting. When I change the code as I suggested (not only partial, I will paste all this time) it works without problems in my computer.:

Sub CopyRows()

Dim RowNum As Integer
Dim pw As String
Dim LastRow As Long
LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
pw = "Test"
    Rows(LastRow & ":" & LastRow - 1).Activate
    ActiveSheet.Unprotect Password:=pw
    Selection.Copy
    Rows(LastRow + 2).Select
    Application.CutCopyMode = xlCopy
    ActiveSheet.Paste
    ActiveSheet.Range("B" & LastRow + 2 & ":" & "K" & LastRow + 2).ClearContents
    ActiveSheet.Range("B" & LastRow + 1).Locked = False
    
    ActiveSheet.Protect Password:=pw & _
                        AllowFormattingCells & _
                        AllowFormattingRows & _
                        AllowFormattingColumns
End Sub

Anyways, it is good to hear it is solved.

 

[DanteAmor]

I'm glad to know that you already have a solution.