Forum Discussion
VBA PROTECT SHEET
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
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.
