unprotect next sheet

[ADK]

I have this code below which does not work with the next sheet. I think it
is ActiveSheet.Unprotect ("password") for the CompletionTable sheet. What is
wrong with the code not making it work past the first sheet?

Private Sub test_Click()
ActiveSheet.Unprotect ("password")
Dim lHiddenRws As Long
With Cells.SpecialCells(xlCellTypeVisible)
lHiddenRws = .Areas(1).Rows.Count + 1
.Areas(1)(lHiddenRws, 1).EntireRow.Hidden = False
Range("A1").CurrentRegion.Rows(Range("A1") _
.CurrentRegion.Rows.Count).Copy Destination:= _
Range("A1").CurrentRegion.Rows(Range("A1").CurrentRegion.Rows.Count + 1)
End With
ActiveSheet.Protect ("password")
Sheets("CompletionTable").Select
ActiveSheet.Unprotect ("password")
Dim lHiddenRwsb As Long
With Cells.SpecialCells(xlCellTypeVisible)
lHiddenRwsb = .Areas(1).Rows.Count + 1
.Areas(1)(lHiddenRwsb, 1).EntireRow.Hidden = False
Range("A1").CurrentRegion.Rows(Range("A1") _
.CurrentRegion.Rows.Count).Copy Destination:= _
Range("A1").CurrentRegion.Rows(Range("A1").CurrentRegion.Rows.Count + 1)
End With
ActiveSheet.Protect ("password")
Sheets("PDSR").Select
End Sub

 

[Mike H]

Hi,

Does this help?

Sub stance()
Dim wSheet As Worksheet
For Each wSheet In Worksheets
wSheet.Select

'Do your stuff here

Next wSheet
End Sub

Mike

 

[Tom Ogilvy]

My guess would be that the password is not "password" or whatever string you
are really using.

 

[ADK]

the code goes to debug mode with an eror:

Run-time error '1004'

You cannot use this command on a protected sheet. To uprotect........


So the code is not unprotecting the second sheet before moving on with the
code


any ideas?

 

[Tom Ogilvy]

Try being more explicit
Activesheet.unprotect Password:="ABC"

this is not an asynchronous command, so it will be executed before moving on.

 

[ADK]

still failed. stops at this line of code with that error message:

With Cells.SpecialCells(xlCellTypeVisible)

 

[ADK]

I did a test with the following code and this one worked (both sheets
unprotected before test and after test both sheets were protected)....so
that tells me something other than the protect/uprotect portion is failing.

any ideas?


Private Sub TestMeNow_Click()
ActiveSheet.Unprotect ("123")
ActiveSheet.Protect ("123")
Sheets("CompletionTable").Select
ActiveSheet.Unprotect ("123")
ActiveSheet.Protect ("123")
Sheets("PDSR").Select
End Sub