unprotect next sheet

  • Thread starter Thread starter ADK
  • Start date Start date
A

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
 
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
 
My guess would be that the password is not "password" or whatever string you
are really using.
 
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?
 
Try being more explicit
Activesheet.unprotect Password:="ABC"

this is not an asynchronous command, so it will be executed before moving on.
 
still failed. stops at this line of code with that error message:

With Cells.SpecialCells(xlCellTypeVisible)
 
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
 
Back
Top