Specified Cell range .ClearContents Error

[Kemidan2014]

I have a piece of code that worked perfectly until recently and now it is throwing up an error. Here is the odd bit, i can F8 step through it and it goes through just fine but when i use the assigned macro button it gives me this error 50% of the time.

The only other addition to the work book as a whole is a new worksheet that does not use the sheet i am trying to macro. Is this purely a "too much going on" issue?

Hitting Debug Highlights this line

The only thing that i added to this macro was unprotecting and protecting Worksheets("AACT")

Sub Filter_Data()
    Worksheets("Complaints").Unprotect Password:="Secret"
    Worksheets("AACT").Unprotect Password:="Secret"
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet
    Set srcWS = Sheets("Complaints")
    Set desWS = Sheets("AACT")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lastrowb = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    desWS.Range(Cells(2, 1), Cells(50, 49)).ClearContents
    With srcWS
        .Range("AV2:AV" & LastRow).Formula = "=IF(AND(D2=""0101-6"",M2>=TODAY()-7),""true"",IF(AND(D2=""0101-6"",ISBLANK(M2)),""true"",""false""))"
        .Cells(1).CurrentRegion.AutoFilter 48, "true"
        .Range("A2:AK" & LastRow).SpecialCells(xlCellTypeVisible).Copy
        With desWS
            .Cells(.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
            .Columns.AutoFit
        End With
        .Range("A1").AutoFilter
        .Columns("AV").ClearContents
    End With
    With desWS
        .Range(Cells(2, 27), Cells(50, 28)).ClearContents
        .Range("AA2:AA" & lastrowb).Formula = "=IF(ISBLANK(J2),"" "",IF(ISBLANK(P2),Today()-J2,P2-J2))"
        .Range("AB2:AB" & lastrowb).Formula = "=IF(ISBLANK(K2),"" "",IF(ISBLANK(S2),Today()-K2,S2-K2))"
    End With
    Application.ScreenUpdating = True
    Worksheets("Complaints").Protect Password:="Secret"
    Worksheets("AACT").Protect Password:="Secret"
    ThisWorkbook.Save
End Sub

 

[Joe4]

Have you placed this in a sheet module, or a general module?
If it is in a sheet module, try moving it to a general module in the sheet, and try running it again.

 

[Kemidan2014]

It is already a General Module

 

[Joe4]

The sheet needs to be active before you can use the ClearContents command on it.
So add this before the line giving you the error.

desWS.Activate

 

[Kemidan2014]

I tried that and it worked. not sure why all of a sudden i needed to activate it but im not complaining

I might try it with With and End With so i dont have use .activate

 

[Joe4]

I tried that and it worked. not sure why all of a sudden i needed to activate it but im not complaining

If you are already on that sheet when it hits that line of code, then you do not need to activate it.
But if you are on a different sheet, then you do.
So your introduction of the new sheet may have changed that for you.

 

[Kemidan2014]

I am thinking that might be it, it runs alot of formulas so i bet its quickly activating the other sheet when its recalculating? you HAVE to be on the target sheet that macro is running for because its a button.

 

[RoryA]

The sheet does not need to be active, you just need to properly qualify the Cells properties:

desWS.Range(desWS.Cells(2, 1), desWS.Cells(50, 49)).ClearContents

 

[Joe4]

The sheet does not need to be active, you just need to properly qualify the Cells properties:

desWS.Range(desWS.Cells(2, 1), desWS.Cells(50, 49)).ClearContents

Ah yes, I always forget that!