Forum Discussion

Carl_Stephens's avatar
Carl_Stephens
Copper Contributor
Sep 15, 2020
Solved

VBA code locks out autofilter

Hello People,

 

The below code runs perfectly, however, the password also locks of the ability to use autofilters after the code has run......can someone kindly advise the code that I need to add so that the autofilter works after the code password protects the sheet. Thank you in advance.

 

Sub CoypFilteredData()


Dim wsData As Worksheet
Dim wsDest As Worksheet
Dim lr As Long

Application.ScreenUpdating = False

Set wsData = Worksheets("1. MAPS List")
Set wsDest = Worksheets("2. Joiners List")

wsData.Unprotect ("ML")


lr = wsData.Cells(Rows.Count, "AP").End(xlUp).Row

If wsData.FilterMode Then wsData.ShowAllData

With wsData.Rows(1)
.AutoFilter Field:=42, Criteria1:="Not On Joiners List"
.AutoFilter Field:=43, Criteria1:="<90"
If wsData.Range("H1:H" & lr).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
wsData.Range("AR2:AU" & lr).SpecialCells(xlCellTypeVisible).Copy
wsDest.Range("AB" & Rows.Count).End(3)(2).PasteSpecial Paste:=xlPasteValues
wsDest.Select
End If
.AutoFilter Field:=42
.AutoFilter Field:=43
Range("AP1").Select
wsData.Protect ("ML")
End With
Application.ScreenUpdating = True
End Sub

2 Replies

  • Carl_Stephens 

    Change

    wsData.Protect ("ML")

    to

    wsData.EnableAutoFilter = True
    wsData.Protect Password:="ML", UserInterfaceOnly:=True

    • Carl_Stephens's avatar
      Carl_Stephens
      Copper Contributor
      Thank you again Hans.....I really appreciate it. I am still new to VBA coding and learning, and some things I simply cannot work out lol. Thank you again.

Resources