excel: advanced filter with protected sheet

Question

I have this sheet where I use Advanced Filter to search for informations inside another sheet in my workbook.

In addition, I want to protect sheet because I have some formulas on cells that people shouldn't be able to change but I also have cells that the user should come with some information then I've already unlocked these cells as you can see below:

The problem is when I try to run my Advanced Filter when I click my "Filter" button. I get an error message that says:

Advanced Filter can't run in Protected Sheet.

So I associated this code to my "Filter" button:

Private Sub Filtrar_Click()

Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
    wks.Unprotect "Password"
    Call LimparAntes
    wks.Protect "Password", UserInterfaceOnly:=True
Next

End Sub

The LimparAntes sub is the routine that calls Advanced Filter, but I still get the same mistake so I'm in doubt. The code is below:

Sub LimparAntes()
'
' LimparAntes Macro
'

'
Dim Lastrow As Long
Lastrow = Sheets("AUX").Range("A" & rows.Count).End(xlUp).Row

    Sheets("AUX").Range("A1:K" & Lastrow).AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Sheets("CONSULTA").Range("D34:I35"), CopyToRange:=Sheets("CONSULTA").Range("B40:K40"), Unique:= _
        False
    Sheets("CONSULTA").Range("F37").Select

End Sub

Is that the correct way to do that? I've made a lot of research but I couldn't find anyone with the same problem with Advanced Filter as I so I don't even know if it's possible to achieve what I want.

excel: advanced filter with protected sheet

1 Answers