Paste after unprotecting worksheet not working

Question

I have two sheets "Request" and "Changes" both protected by same password. Password has been stored as a constant in another module. I have tested this code when "Changes" sheet in unprotected and it works, however for some reason, if I have to unprotect the sheet before pasting it won't paste.

I am simplifying trying to keep a record of what was deleted in the workbook by placing the info in a protected sheet "Changes" that is hidden.

Sub PODelete()
Dim rng As Range, ws As Worksheet, ws1 As Worksheet, lr As Integer
Set ws = Sheets("Request")
Set ws1 = Sheets("Changes")
lr = ws1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row 'Last non-blank row for column A

On Error Resume Next
Set rng = Application.InputBox("Select a range", Type:=8)
If Not rng Is Nothing Then
    ' Checks to see if column A is selected
    If rng.Cells.Column = 1 Then

        ' Checks to see that only 1 cell is selected
        If rng.Cells.Count > 1 Then
            MsgBox "Select only one P.O. Number to remove"
        Else

            ' Select entire row
            rng.EntireRow.Copy
            ' Activate sheet and pastes values
            Sheets("Changes").Activate
            ws1.Unprotect Password:=worksheetpassword
            Cells(lr, 1).EntireRow.Select
            Selection.PasteSpecial Paste:=xlPasteValues

            ws1.Protect Password:=worksheetpassword, DrawingObjects:=True, Contents:=True, Scenarios:=True
            ' Deletes range
            ws.Activate
            ws.Unprotect Password:=worksheetpassword
            rng.EntireRow.Delete
            ws.Protect Password:=worksheetpassword, DrawingObjects:=True, Contents:=True, Scenarios:=True

        End If
    Else
        MsgBox "Select P.O. Number to remove"
    End If

Else
    Exit Sub
End If

End Sub

Once again, if I run this macro with the "Changes" sheet unlocked it will work.

Answer 1

The real answer to this is actually not to unprotect the Worksheet at all. Protect it with a different password that isn't stored in the file anywhere, but set the parameter UserInterfaceOnly:=True. This will allow the macro to make changes to the worksheet, and even if the user find the hidden sheet they won't be able to find the password.

Speaking of hiding it, you should really hide it. As far as I know this is only possible through VBA, but you can set the Worksheet to xlSheetVeryHidden, either through the VBE...

...or in code:

Worksheets("Changes").Visible = xlSheetVeryHidden

Then password protect the VBA project with the same password as the Worksheet, and nobody is going to find it but you unless they know the name of the sheet and manually type it into a cell formula:

=Changes!A1

If you want to avoid that possibility, just name the Worksheet something non-obvious or even total gibberish. The likelihood of an unsuspecting user typing this into a cell...

=kusidkeshlkiehas!A1

...is virtually zero.

Answer 2

I tried to copy a range from one work book(selection.copy only once and keeping the workbook open) and through "for loop", to many other work books(ActiveSheet.Unprotect "password", ActiveSheet.Paste).

This works fine during 1st trial but fails during subsequent trials on the same file with a message "Error 1004 Paste Method of Worksheet Failed" suggested by Mr Darrel Dixon and various other solutions of "UserInterfaceOnly:=True" etc but the error is repeating. The sheet appears UNPROTECTED at that stage and copy from source workbook and paste into destination workbook MANUALLY works fine but through VBA macro, it fails.

Finally, I tried repeating "selection.copy" from source workbook after "ActiveSheet.Unprotect" on each destination workbook/each iteration of for loop.

Even though this appears to be inefficient code, this alone solved the problem of PASTE failure after UNPROTECTING a worksheet.

Answer 3

I have found that occasionally doing an Unprotect on the active sheet will change the active sheet to a different sheet. I haven’t figured out under what circumstances this occurs or why the particular sheet that ends up selected gets selected. Could this be your problem? I solved this problem by having one routine that is called to do all Unprotects. In that routine I save the active sheet name before doing the Unprotect and then re-select the saved sheet. I haven't seen this problem with Protecting a sheet but just to be safeI have done the same thing with a Protect routine.