I've got a small macro that's giving me problems, and I don't understand why. Sheets WS and WS2 are both protected, and on the settings for the protection I've checked "Allow Autofilter". On sheet WS2 the user selects a value in a data validation cell (E1). Then my macro takes that value to apply to the autofilter in WS. However, I'm getting a Run-Time error 1004 saying I can't run this command on a protected sheet (line beginning "ws.Range("N:N")...). Any help on this would be immensely appreciated.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet: Set ws = Sheets("View1Pivot")
Dim ws2 As Worksheet: Set ws2 = Sheets("Chart-View 1")
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$E$1" Then
ws.Range("N:N").AutoFilter Field:=1, Criteria1:=ws2.Range("E1").Text
End If
End Sub
Bookmarks