VBA xlPasteValues fails on 2nd run

Question

I have a short code that basically copies a selected row to another sheet, and deletes it from the first.

For some reason when I run this, it will run just fine, if I then select a different row, I get a pasteSpecial method of RangeClass failed error.

Sub Completed()

Dim trackerRow As Integer, compRow As Integer, answer As Integer
Application.ScreenUpdating = False
Application.CutCopyMode = False
trackerRow = ActiveCell.Row
compRow = Sheets("Completed").Cells(Rows.Count, "B").End(xlUp).Row
If trackerRow < 3 Then Exit Sub
    If Cells(trackerRow, 2) = "" Then
        MsgBox "This row is empty, please select the candidate you want to move.", vbExclamation, "Empty Row"
    Else
    answer = MsgBox("Are you sure you want to move " & Cells(trackerRow, 3).Value & "?", vbYesNo, "Confirm move?")
    If answer = vbYes Then
        'move row
        Rows(trackerRow).EntireRow.Copy
        With ThisWorkbook
            With .Sheets("Completed")
                .Activate
                .Unprotect "HSBC2017"
                .Cells(compRow + 1, 1).PasteSpecial xlPasteValues '**error line**.
                .Protect "HSBC2017"
            End With
            With .Sheets("Tracker")
                .Unprotect "HSBC2017"
                .Rows(trackerRow).EntireRow.Delete
                .Protect "HSBC2017"
                .Activate
            End With
        End With
    End If
End If
Application.ScreenUpdating = True
Application.CutCopyMode = False
End Sub

I can't work out why when I open the document it will work once, but you have to re-open to run a second time?

Side note: If you protect your sheets with `.Protect Password:="HSBC2017", UserInterfaceOnly:=True` once, then you don't need to unprotect it for any macro actions later. This way the sheet is only protectet from user actions but macros can still run. See [Worksheet.Protect Method (Excel)](http://msdn.microsoft.com/en-us/library/office/ff840611.aspx). — Pᴇʜ, Apr 26 '17 at 15:16
I'd suggest you do the copy immediately before the paste: `Sheets("Tracker").Rows(trackerRow).EntireRow.Copy` directly before `.Cells(compRow + 1, 1).PasteSpecial xlPasteValues` — Rory, Apr 26 '17 at 15:16
@Rory that would change the row copied as he hasn't declared it explicitly — Tom, Apr 26 '17 at 15:19
@Tom From the context, it seems pretty likely that that is the sheet to copy from. — Rory, Apr 26 '17 at 15:24
related: [Should I turn .CutCopyMode back on before exiting my sub procedure?](http://stackoverflow.com/questions/33833318/should-i-turn-cutcopymode-back-on-before-exiting-my-sub-procedure) — , Apr 26 '17 at 15:25

score 2 · Accepted Answer · edited May 23 '17 at 11:46

Each time you crash into a debug, you leave the destination Completed worksheet unprotected. Closing the workbook and reopening it leaves it unprotected.

Unprotecting an unprotected worksheet does not cancel the .CutCopyMode but unprotecting a protected worksheet does.

So in the first run-through with an unprotected destination worksheet there is something on the clipboard (and you have 'dancing ants' on the row-to-copy on the Tracker worksheet). Since everything runs through, the destination Completed worksheet is now protected and a second run-through will cancel the .CutCopyMode when the worksheet is unprotected. You can watch this by stepping through your code with F8 and see the 'dancing ants' disappear from the copy area on the Tracker worksheet as soon as you unprotect the Completed worksheet a second time.

Possible solutions (with a nod to comments from Peh and Rory):

Protect the worksheet with a password and UserInterfaceOnly.

with worksheets("Completed")
    .Protect Password:="HSBC2017", UserInterfaceOnly:=True
end with

This only has to be done once and you can remove both the .Unprotect and .Protect code from future sub procedure operations. Only actions made manually by the user will be restricted.

Initiate the copy from the source worksheet after unprotecting the destination worksheet.

with worksheets("Completed")
    .Unprotect Password:="HSBC2017"
    worksheets("Tracker").Rows(trackerRow).EntireRow.Copy
    .Cells(compRow + 1, 1).PasteSpecial xlPasteValues
    .Protect Password:="HSBC2017"
end with

That explanation makes perfect sense, I will be mindful of this in the future. And thanks to [Peh](http://stackoverflow.com/users/3219613/peh) for the the `UserInterfaceOnly`, something I was not aware of but will be very useful. — Tim Wilkinson, Apr 27 '17 at 09:10

VBA xlPasteValues fails on 2nd run

1 Answers1