VBA fails when worksheet is protected?

[EssKayKay]

Hello,

I have a VBA routine that runs fine if the worksheet is unprotected. However, if it is protected it fails with a
Run-time error ‘1004’ -- Paste method of Worksheet class failed

It fails when attempting to run ActiveSheet.Paste. I placed a Sheets("Amortize").Unprotect line just prior to the ActiveSheet.Paste hoping this would unprotect the worksheet and allow the code to proceed – it still fails.

Here is one of the routines that is having the issue –

Sub MaxData()
' Sets up Maximum DATA range to 2000 payments
' Keyboard Shortcut: Ctrl+e

Application.ScreenUpdating = False
Rows("34:2032").Select

     Sheets("Amortize").Unprotect                               '<=== This Unprotect works fine here ===

     Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
     Range("B2035:L2035").Select
     Selection.Copy 
     Range("B2032").Select
     Range(Selection, Selection.End(xlUp)).Select

     Sheets("Amortize").Unprotect                              '<=== This Unprotect does not work ===
     ActiveSheet.Paste                                                 '<=== This is where it fails ===
     Application.CutCopyMode = False
     Application.Run "Sheet1.cmdHome_Click"
End Sub

I also created a subroutine that unprotects the worksheet. I placed this in the above routine instead of the Sheets("Amortize").Unprotect but this too failed.

Sub UnProtect_It()
   Dim Ws As Worksheet
   For Each Ws In ActiveWorkbook.Worksheets
        Ws.Unprotect
    Next Ws
End Sub

Any suggestions would be appreciated.

Thanks for viewing,
Steve

 

[EssKayKay]

I'm still interested in fixing the problem above but I coded a workaround. It's not really the answer but rather a patch. It appears to be working.

Here's the patch

Sheets("Amortize").Unprotect
    Range("B2035:K2035").Select
    Selection.Copy
    Range("B2032").Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

Steve K.