Copying in sheet with protection with the use of a macro

  • Thread starter Thread starter Swingleft
  • Start date Start date
S

Swingleft

Hi,

I have a worksheet with protected cells and unprotected cells,
the worksheet is protected with a pasword.

After I entered all the data in the worksheet in the unprotected cells,
i need to copy and paste "only the values​​" of the whole worksheet. As
long as the worksheet is
unprotected it works fine but if i protect the sheet i get an error.

I tried to record a macro in which i protect and unprotect the worksheet but
that didn't work.

so does anyone know how i can get the security off (with a password),do the
copy and paste
and then set the security with pasword back.


Thanks for helping ..
 
Hi Swingleft

You can do a google search for AllInternalPasswords.xls, it finds and
removes protection.

I have a protected WB/Sheet supplied by another company, I have my own
version of it unprotected, I do my thing, then protect it when I am finished
and save it the way they like it and send it on to them..

Something to ponder.

Here is a snippet you can play with.

Replace the Source** & Dest** references with your own...

Windows("SourceWb.xls").Activate
Sheets("SourceSh").Select
Range("SourceRng").Select
Selection.Copy

Windows("DestWb.xls").Activate
Range("DestCell").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

ActiveSheet.Protect

Although, the Guru's will have a cleaner/faster way of copying the cells
directly..

I'm nowhere near Guru status, and never likely to be, but it fun learning
and helping others along the way....


Good luck

HTH
Mick
 
Sub qwert()
With ActiveSheet
.Unprotect Password:="justme"
.Cells.Copy
.Range("A1").PasteSpecial Paste:=xlPasteValues, operation:=xlNone
.Protect Password:="justme"
End With
End Sub


Gord Dibben MS Excel MVP
 
Swingleft explained :
Hi,

I have a worksheet with protected cells and unprotected cells,
the worksheet is protected with a pasword.

After I entered all the data in the worksheet in the unprotected cells,
i need to copy and paste "only the values​​" of the whole worksheet. As long
as the worksheet is
unprotected it works fine but if i protect the sheet i get an error.

I tried to record a macro in which i protect and unprotect the worksheet but
that didn't work.

so does anyone know how i can get the security off (with a password),do the
copy and paste
and then set the security with pasword back.


Thanks for helping ..
Click to expand...

It can be done much simpler...

Range("Target").Value = Range("Source").Value

...where each range containing the values is fully defined OR fully
referenced.
 
Hi,
I'm learning vba when i need to do something, and i found something
that you could look at :

'on the ThisWorkbook "module" or object
Private Sub Workbook_Open()
With Worksheets("Sheet1")
.Protect "sos", UserInterfaceOnly:=True
End With
End Sub

Hope this helps
Pascal
 
bpascal123 wrote on 6/4/2011 :
Hi,
I'm learning vba when i need to do something, and i found something
that you could look at :

'on the ThisWorkbook "module" or object
Private Sub Workbook_Open()
With Worksheets("Sheet1")
.Protect "sos", UserInterfaceOnly:=True
End With
End Sub

Hope this helps
Pascal
Click to expand...

What is it exactly that you want me to look at? I would write this code
like this...

With Sheets("Sheet1")
.Unprotect Password:="sos"
.Protect Password:="sos", UserInterfaceOnly:=True
End With

...because applying protection to an already protected sheet doesn't
work unless you unprotect it first. In this case, UserInterfaceOnly
does not persist as do some other protection permissions and so must be
applied whenever the file is opened.

I use a ResetProtection routine that first unprotects, then calls the
WksProtect routine. Both routines accept an Optional arg for passing a
ref to the target sheet.
 
Back
Top