Excel 2003: Protection/PasteSpecial weirdness

[Kimed]

Hi,

I have a sheet with a button with following script on it:

Range("SourceRange").Copy

ActiveSheet.Unprotect
Range("TargetRange").PasteSpecial (xlPasteValues)
ActiveSheet.Protect

I found that half of time the code throws a "1004: PasteSpecial method of Range class failed" error. After some experimenting I discovered that when the code breaks after the Unprotect command, the next run is successful. But after a successful run the sheet becomes protected, and the run next to it fails, *despite* the explicit command to unprotect it in the code above. If I manually unprotect or protect the sheet before running the code, result is always a success or failure respectively. The most weird part is that the effect stays even though the target area itself doesn't contain locked cells at all (it normally *should* be locked, but I removed the lock when seeking to determine the root of a problem), so the paste operation fails even though there's nothing to protect. Trying to paste manually meets no difficulty, only the scripted sequence of commands fails.

Can somebody explain reasons of such a weird behavior?

Thanks.

 

[SkipVought]

hi,

ActiveSheet.Unprotect
Range("SourceRange").Copy

Range("TargetRange").PasteSpecial (xlPasteValues)
ActiveSheet.Protect

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]