Macro protecting & Unprotecting sheet showing error.

[coolhit]

Hi

I have a button which carries out following instruction.

Private Sub CmdTransferStorToUsed_Click()

Dim LR As Long, i As Long 'date & name
Dim rFind As Range, sFind As String 'slot transfer

Sheets("Consumables Used From AM Rack").Unprotect consumables 'unprotect sheet

sFind = Range("K13").Value ' part of slot transfer

With Sheet6.Columns(2)
Set rFind = .Find(What:=sFind, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Not rFind Is Nothing Then
With Sheet8.Cells(Rows.Count, 2).End(xlUp)(2)
.Value = rFind.Offset(, 1).Value
.Offset(, 1).Value = rFind.Offset(, 3).Value
End With
rFind.Offset(, 1).Clear
rFind.Offset(, 3).Clear
End If
End With

With Sheets("Main Menu") 'part of date & name
'LR = .Range("M" & Rows.Count).End(xlUp).Row
'For i = 19 To LR - 2 Step 4
'With .Range("M" & i)
With .Range("M19") ' new
.Copy Destination:=Sheets("Consumables Used From AM Rack").Range("A65535").End(xlUp).Offset(1, 0)
.ClearContents
'With .Offset(2)
With .Offset(1) ' new offset 1 row, (ie from M19 to M20)
.Copy Destination:=Sheets("Consumables Used From AM Rack").Range("D65535").End(xlUp).Offset(1, 0)
.ClearContents
End With
End With
'Next i
End With

Sheets("Consumables Used From AM Rack").Protect consumables, Structure:=True, Windows:=False 'proctects the sheet

User press the button in main menu which in turn should unprotect the sheet carry out intruction and then protect the sheet. As the protected sheet is not active, debug show error when unprotecting.

In the forum there is a solution its say Activesheet.unprotect password.

as i have has sheets("??").unprotect password (above in red).

Please advice what i have done wrong.

many thanks.

[arthurbr]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[arthurbr]

Thread closed Op didn't bother although logging in after remark