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

sparky2205

Active Member
Joined
Feb 6, 2013
Messages
474
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
Code:
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Try to unprotect the worksheet before Selection.Copy method.

Code:
'...
    ActiveSheet.Unprotect Password:=pw
    Selection.Copy
    Rows(LastRow + 2).Select
'...
 
Upvote 0
Move this line to the end of the code

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

Code:
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.
 
Upvote 0
Solution
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.
 
Upvote 0
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.:

Code:
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.
 
Upvote 0
I'm glad to know that you already have a solution.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top