Simple way to get around protect/unprotect sheet

[Beginner35]

Hello, i have been so frustrated with this below:

I have about 100 files and they all have this tab call "Oracle". Some of the file the "Oracle" tab is protected, some are not protected, some are protected with a password call 'ch'. My co-worker and I have been working on this issue but doesn't seem to able to find a simple solution. The end goal is to un-protect this Oracle sheet within a file doesn't matter there is password or no password or not protected to begin with and copy and paste the data on that tab into a new file. 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
helen.Sheets("Oracle").Visible = True
helen.Sheets("Oracle").Unprotect Password:="ch"


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


End Sub

 

[RoryA]

Try:

helen.Activate
with helen.Sheets("Oracle")
.Visible = True
if .protectcontents then .Unprotect Password:="ch"
End With

 

[Beginner35]

I kept getting Runtime error Object required. it highlighted
If.PrectectContents Then.Unprotect Password:="ch"

where am I going wrong? It should be simple to unprotect.

 

[Beginner35]

It also looks like when I try to un-protect some Oracle tabs don't have a password so it can't handle unprotect Password:="ch". Some have this password some were protected without password. I am not sure that makes a difference or not.

 

[RoryA]

If you use the code I posted you will not get the error.

 

[Beginner35]

hi here is copy and paste of my whole thing. I realized when I typed it in my last reply there was spelling error. It is still showing error to unprotect

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
helen.Sheets("Oracle").Visible = True
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

 

[RoryA]

That is not what I posted. This part:

helen.Sheets("Oracle").Visible = True
With helen.Sheets("Oracle").Visible = True
If .ProtectContents Then .Unprotect Password:="ch"
End With

should be:

With helen.Sheets("Oracle")
.Visible = True
If .ProtectContents Then .Unprotect Password:="ch"
End With

 

[Beginner35]

hi Rory:

Now I am getting the error I used to get by play with the code saying I need to unprotect sheet in order to filter. Blow is the error that I get now using your exact code.

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

 

[Beginner35]

It seems like when I get the
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

It does no like the code:
Range("a5:au1228").AutoFilter Field:=29, Criteria1:="y"
Range("a5:au1228").Copy helen2.Sheets(1).Range("a5")

It still thinks it is a protected sheet even thought I unprotected it with code above..

 

[RoryA]

It may simply be that you have not specified the sheet for the autofilter command:

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")
   
   With helen.Sheets("Oracle")
      .visible = True
      If .ProtectContents Then .Unprotect Password:="ch"
      .Range("a5:au1228").AutoFilter Field:=29, Criteria1:="y"
      .Range("a5:au1228").Copy helen2.Sheets(1).Range("a5")
   End With

End Sub