Unable to sort on protected sheet

[honkin]

I have a template I use to create new results sheets. The first 16 rows are locked and have boxes with Profit & Loss and other metrics and the data headers are in row 17 with the data all starting from row 18.

As I said, the first 16 rows are locked and when I protect the sheet, I select the things to allow, like Sort, Filter & Select Unlocked Cells, yet when I click on the Data menu, Filter is greyed out.

Any ideas why this is the case and how is it possible to have the sheet perform as I wish?

cheers

 

[antman2988]

Let me know if this works for you.

Option Explicit

Sub ClearFilters()
    Dim ws As Worksheet
   
    For Each ws In Worksheets
        If ws.AutoFilterMode Then
            ws.AutoFilter.ShowAllData
            ActiveSheet.Protect Contents:=True, AllowFiltering:=True
        Else
            MsgBox "No AutoFilter detected on ." & ws
        End If
    Next ws
End Sub

 

[honkin]

Are there multiple sheets or just one?

just the one mate.

 

[antman2988]

just the one mate.

The code I posted should still work on one sheet. Let me know if it doesn't.

 

[honkin]

Let me know if this works for you.

Option Explicit

Sub ClearFilters()
    Dim ws As Worksheet
  
    For Each ws In Worksheets
        If ws.AutoFilterMode Then
            ws.AutoFilter.ShowAllData
            ActiveSheet.Protect Contents:=True, AllowFiltering:=True
        Else
            MsgBox "No AutoFilter detected on ." & ws
        End If
    Next ws
End Sub

Excellent antman2988. I will see how it goes and let you know

cheers and thanks so much

 

[antman2988]

Excellent antman2988. I will see how it goes and let you know

cheers and thanks so much

You're welcome! Did it work for you?

 

[honkin]

The code I posted should still work on one sheet. Let me know if it doesn't.

Just got a chance to test this and it seems to work fine, make all the contents now appear and keeping the autofilter active

Thanks so much