VBA PROTECT SHEET

%3CLINGO-SUB%20id%3D%22lingo-sub-1654126%22%20slang%3D%22en-US%22%3EVBA%20PROTECT%20SHEET%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1654126%22%20slang%3D%22en-US%22%3EHello%20All%2C%3CBR%20%2F%3E%3CBR%20%2F%3EApologies%20if%20this%20has%20been%20answered%20previously.%20The%20below%20code%20does%20not%20work%20when%20I%20protect%20the%20sheet....and%20I%E2%80%99ve%20tried%20adding%20a%20password%20unprotect%20and%20protect%20to%20the%20code%20but%20cannot%20seem%20to%20make%20it%20work.%20Can%20someone%20kindly%20advise%20where%20I%20need%20to%20add%20the%20unprotect%20and%20protect%20code%20in%20the%20below%20script%20for%20it%20to%20work%20on%20a%20protected%20sheet.%20Thank%20you%20all%20in%20advance.%3CBR%20%2F%3E%3CBR%20%2F%3ESub%20CoypFilteredData()%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20wsData%20As%20Worksheet%3CBR%20%2F%3EDim%20wsDest%20As%20Worksheet%3CBR%20%2F%3EDim%20lr%20As%20Long%3CBR%20%2F%3E%3CBR%20%2F%3EApplication.ScreenUpdating%20%3D%20False%3CBR%20%2F%3E%3CBR%20%2F%3ESet%20wsData%20%3D%20Worksheets(%221.%20MAPS%20List%22)%3CBR%20%2F%3ESet%20wsDest%20%3D%20Worksheets(%222.%20Joiners%20List%22)%3CBR%20%2F%3E%3CBR%20%2F%3Elr%20%3D%20wsData.Cells(Rows.Count%2C%20%22AP%22).End(xlUp).Row%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20wsData.FilterMode%20Then%20wsData.ShowAllData%3CBR%20%2F%3E%3CBR%20%2F%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%3EEnd%20With%3CBR%20%2F%3EApplication.ScreenUpdating%20%3D%20True%3CBR%20%2F%3EEnd%20Sub%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1654126%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1654217%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20PROTECT%20SHEET%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1654217%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%20Just%20unprotect%20at%20the%20beginning%20and%20protect%20at%20the%20end%3A%3C%2FP%3E%3CP%3E...%3C%2FP%3E%3CP%3E%3CSPAN%3ESet%20wsData%20%3D%20Worksheets(%221.%20MAPS%20List%22)%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ESet%20wsDest%20%3D%20Worksheets(%222.%20Joiners%20List%22)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EwsDATA.unprotect()%3C%2FP%3E%3CP%3EwsDest.unprotect()%3C%2FP%3E%3CP%3E...%3C%2FP%3E%3CP%3EwsDATA.protect()%3C%2FP%3E%3CP%3EwsDest.protect()%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20have%20a%20password%20you%20use%20that%20will%20have%20to%20be%20added%20to%20each%20statement.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor
Hello All,

Apologies if this has been answered previously. The below code does not work when I protect the sheet....and I’ve tried adding a password unprotect and protect to the code but cannot seem to make it work. Can someone kindly advise where I need to add the unprotect and protect code in the below script for it to work on a protected sheet. Thank you all 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")

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
End With
Application.ScreenUpdating = True
End Sub
2 Replies

@Carl_Stephens  Just unprotect at the beginning and protect at the end:

...

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

wsDATA.unprotect()

wsDest.unprotect()

...

wsDATA.protect()

wsDest.protect()

End Sub

 

If you have a password you use that will have to be added to each statement.

Thank you. I was using a different protect code and noted that the wsData.unprotect worked perfect. Thank you again.