SOLVED

VBA code locks out autofilter

%3CLINGO-SUB%20id%3D%22lingo-sub-1668057%22%20slang%3D%22en-US%22%3EVBA%20code%20locks%20out%20autofilter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1668057%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20People%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20below%20code%20runs%20perfectly%2C%20however%2C%20the%20password%20also%20locks%20of%20the%20ability%20to%20use%20autofilters%20after%20the%20code%20has%20run......can%20someone%20kindly%20advise%20the%20code%20that%20I%20need%20to%20add%20so%20that%20the%20autofilter%20works%20after%20the%20code%20password%20protects%20the%20sheet.%20Thank%20you%20in%20advance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20CoypFilteredData()%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EDim%20wsData%20As%20Worksheet%3CBR%20%2F%3EDim%20wsDest%20As%20Worksheet%3CBR%20%2F%3EDim%20lr%20As%20Long%3C%2FP%3E%3CP%3EApplication.ScreenUpdating%20%3D%20False%3C%2FP%3E%3CP%3ESet%20wsData%20%3D%20Worksheets(%221.%20MAPS%20List%22)%3CBR%20%2F%3ESet%20wsDest%20%3D%20Worksheets(%222.%20Joiners%20List%22)%3C%2FP%3E%3CP%3EwsData.Unprotect%20(%22ML%22)%3C%2FP%3E%3CP%3E%3CBR%20%2F%3Elr%20%3D%20wsData.Cells(Rows.Count%2C%20%22AP%22).End(xlUp).Row%3C%2FP%3E%3CP%3EIf%20wsData.FilterMode%20Then%20wsData.ShowAllData%3C%2FP%3E%3CP%3EWith%20wsData.Rows(1)%3CBR%20%2F%3E.AutoFilter%20Field%3A%3D42%2C%20Criteria1%3A%3D%22Not%20On%20Joiners%20List%22%3CBR%20%2F%3E.AutoFilter%20Field%3A%3D43%2C%20Criteria1%3A%3D%22%26lt%3B90%22%3CBR%20%2F%3EIf%20wsData.Range(%22H1%3AH%22%20%26amp%3B%20lr).SpecialCells(xlCellTypeVisible).Cells.Count%20%26gt%3B%201%20Then%3CBR%20%2F%3EwsData.Range(%22AR2%3AAU%22%20%26amp%3B%20lr).SpecialCells(xlCellTypeVisible).Copy%3CBR%20%2F%3EwsDest.Range(%22AB%22%20%26amp%3B%20Rows.Count).End(3)(2).PasteSpecial%20Paste%3A%3DxlPasteValues%3CBR%20%2F%3EwsDest.Select%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E.AutoFilter%20Field%3A%3D42%3CBR%20%2F%3E.AutoFilter%20Field%3A%3D43%3CBR%20%2F%3ERange(%22AP1%22).Select%3CBR%20%2F%3EwsData.Protect%20(%22ML%22)%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3EApplication.ScreenUpdating%20%3D%20True%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1668057%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1668164%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20code%20locks%20out%20autofilter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1668164%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F787478%22%20target%3D%22_blank%22%3E%40Carl_Stephens%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EChange%3C%2FP%3E%0A%3CP%3EwsData.Protect%20(%22ML%22)%3C%2FP%3E%0A%3CP%3Eto%3C%2FP%3E%0A%3CP%3EwsData.EnableAutoFilter%20%3D%20True%3CBR%20%2F%3EwsData.Protect%20Password%3A%3D%22ML%22%2C%20UserInterfaceOnly%3A%3DTrue%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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
Highlighted
Best Response confirmed by Carl_Stephens (Occasional Contributor)
Solution

@Carl_Stephens 

Change

wsData.Protect ("ML")

to

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

Highlighted
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.