Macro's doesn't work (in potected Sheets)

[Ruud]

When I protected my sheets.
The macro's will give a lot of Errors, in unprotected mode
the macro's work perfect.

Error section is in bold:

Sheets("Venders").Select
ActiveSheet.Unprotect
Range("E5").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Why? what I'm doing wrong?
I changed Paste mode, I checked the space etc.
Thanks for any support
Ruud

 

[papou]

Hello Rudd
You don't say what your macro does before the lines you mention.
So provided this is the whole code you are using, I would suggest that you can't paste unless you have previously copied.
HTH
Cordially
Pascal
"Ruud" <[email protected]> a écrit dans le message de When I protected my sheets.
The macro's will give a lot of Errors, in unprotected mode
the macro's work perfect.

Error section is in bold:

Sheets("Venders").Select
ActiveSheet.Unprotect
Range("E5").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Why? what I'm doing wrong?
I changed Paste mode, I checked the space etc.
Thanks for any support
Ruud

 

[Ruud]

Pascal, thanks for your attention,

But I found it, .... it's an Error in Excel !

"Past" will not always work, if it comes directly after "ActiveSheet.Unprotect"
So I put all the unprotect commando's at the start of every macro.
Now it works fine.

BTW... to unprotect all the sheets, will give a lot of commando's
is there not one like
Sheets("sheet1:sheet10").Select
ActiveSheet.Unprotect
Ruud

Hello Rudd
You don't say what your macro does before the lines you mention.
So provided this is the whole code you are using, I would suggest that you can't paste unless you have previously copied.
HTH
Cordially
Pascal
"Ruud" <[email protected]> a écrit dans le message de When I protected my sheets.
The macro's will give a lot of Errors, in unprotected mode
the macro's work perfect.

Error section is in bold:

Sheets("Venders").Select
ActiveSheet.Unprotect
Range("E5").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Why? what I'm doing wrong?
I changed Paste mode, I checked the space etc.
Thanks for any support
Ruud

 

[papou]

Rudd
If you wih to do a bulk protection (ie all worksheets in workbook) you may try:

Sub protectmysheets()
Dim Ws As Worksheet
For Each Ws In Worksheets
Ws.Protect
Next Ws
End Sub

Sub unprotectmysheets()
Dim Ws As Worksheet
For Each Ws In Worksheets
Ws.Unprotect
Next Ws
End Sub

HTH
Cordially
Pascal
"Ruud" <[email protected]> a écrit dans le message de Pascal, thanks for your attention,

But I found it, .... it's an Error in Excel !

"Past" will not always work, if it comes directly after "ActiveSheet.Unprotect"
So I put all the unprotect commando's at the start of every macro.
Now it works fine.

BTW... to unprotect all the sheets, will give a lot of commando's
is there not one like
Sheets("sheet1:sheet10").Select
ActiveSheet.Unprotect
Ruud

Hello Rudd
You don't say what your macro does before the lines you mention.
So provided this is the whole code you are using, I would suggest that you can't paste unless you have previously copied.
HTH
Cordially
Pascal
"Ruud" <[email protected]> a écrit dans le message de When I protected my sheets.
The macro's will give a lot of Errors, in unprotected mode
the macro's work perfect.

Error section is in bold:

Sheets("Venders").Select
ActiveSheet.Unprotect
Range("E5").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Why? what I'm doing wrong?
I changed Paste mode, I checked the space etc.
Thanks for any support
Ruud