Unprotect sheet then filter - it is not letting me! So frustrating!

Beginner35

New member
Joined
Dec 9, 2013
Messages
1
Reaction score
0
Points
0
Hello:

Below are my code and I posted at other websites but still unable to solve the problem. I have about 100 excel files and each has a tab call "Oracle" Some of them are protected with the password "ch", some are protected with no password and some are not protected. Below code should able to unprotect sheet even if there is no password set to unlock but I still run into below run-time error when I get to Range("a5:au1228").AutoFilter Field:=29, Criteria1:="y"

Run-time Error'1004':
You cannot use this command on a protected sheet. To use this command, you must first unprotect the sheet(Review tab, Changes group, Unprotect Sheet button). You may be prompted for a password

I think maybe it is my autofilter code is not right? Maybe even after I unprotect it is still consider protected and that is why the autofilter does not work? I am desperate, please help!


Sub Pickle()
Dim helen As Workbook
Dim helen2 As Workbook


Set helen2 = Workbooks.Add


Set helen = Workbooks.Open("h:\2014 Baseline\2014_13512 Chief Architect.xlsm")
helen.Activate
With helen.Sheets("Oracle")
.Visible = True
If .ProtectContents Then .Unprotect Password:="ch"
End With


Range("a5:au1228").AutoFilter Field:=29, Criteria1:="y"
Range("a5:au1228").Copy helen2.Sheets(1).Range("a5")


End Sub
 
Back
Top