Extract Worksheets Code Crashing

[cterjesen]

Any idea why this piece of code would generate an error message:

ActiveWindow.SelectedSheets.Copy

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
ActiveSheet.PageSetup.RightFooter = "Extracted from Budget " & Now & Chr(12)
Module1.UnProtectIt
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Cells.Select
Selection.Locked = True
Selection.FormulaHidden = False
Module1.ProtectIt

Next ws

....i get an error on the 4th worksheet it tries to export at the "Selection.Locked = True" line. the message says Locked Range not defined.

Thanks

 

[AlphaFrog]

    Dim ws     As Worksheet

    ActiveWindow.SelectedSheets.Copy
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    For Each ws In ActiveWorkbook.Worksheets
        ws.Activate
        ws.PageSetup.RightFooter = "Extracted from Budget " & Now & Chr(12)
        Module1.UnProtectIt
        With ws.UsedRange
            .Value = .Value
            .Locked = True
            .FormulaHidden = False
        End With
        Module1.ProtectIt
    Next ws
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic

 

[cterjesen]

For some reason it crashes when it reaches certain selected sheets. I get a Run-time error '-2147417848 (80010108) Method 'Locked' of object 'Range' failed. I have to Ctrl+Alt+Del out of Excel and restart when this happens.

Interestingly enough, if I manually try to extract the sheets that the code stops on, I can copy them to another workbook, but then I get a "calculation" messsage on the status bar that won't go away even by hitting F9 and every time I try to enter something in a cell, Excel tries to calculate slowly.

 

[AlphaFrog]

Can you confirm that the code for UnProtectIt is actually unprotecting each worksheet (especially when called from this macro)?

 

[cterjesen]

That's a good point. the code for that is:

ActiveSheet.Protect Password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True


What's funny is, this code works on an earlier version of this file from last year. Enhancements have been added this year, but I can't figure out what would have corrupted this piece.

And it still works for certain sheets, but not others. They all have the same password.