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

sparky2205

Board Regular
Joined
Feb 6, 2013
Messages
151
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
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
880
Try to unprotect the worksheet before Selection.Copy method.

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

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,753
Office Version
2007
Platform
Windows
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.
 

sparky2205

Board Regular
Joined
Feb 6, 2013
Messages
151
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

BatCoder
Joined
Feb 28, 2002
Messages
880
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.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,753
Office Version
2007
Platform
Windows
I'm glad to know that you already have a solution.
 

Forum statistics

Threads
1,077,827
Messages
5,336,617
Members
399,093
Latest member
chado4250

Some videos you may like

This Week's Hot Topics

Top