Another basic problem

So after comming here for advice I've now set up my workbook to leave some sheets protected with only certain areas unlocked to enter data in. Once data has been entered and the user clicks on a button sheets are then unprotected while the data entered is protected.

What I am having a problem with now is copying the data entered and pasting only the values onto other sheets in the workbook.

Basically what is supposed to happen is that once the Biography sheet is completed the macro should take the data from the ranges that users entered and copy this to all the other sheets in the workbook in a white font.

Any help would be appreciated, the current code I'm using is as follows:

Private Sub CommandButton1_Click()
Dim sh As Worksheet
Const PWORDO As String = "XXXX"
Const PWORDL As String = "XXXX1"
For Each sh In ActiveWorkbook.Worksheets
sh.Unprotect Password:=PWORDO
If sh.Name = "Biography" Then
With sh.Range("F13:K13,F16:K16,F19:K19")
.Locked = True
.FormulaHidden = False
Range("F13:K13,F16:K16,F19:K19").Select
Range("F19").Activate
Selection.Copy
Sheets("Sheet1").Select
End With
Else
With sh
.Cells.Locked = False
.Cells.FormulaHidden = False
.Range("A100:C103").Locked = True
.Cells.FormulaHidden = False
.Select
.Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
End If
Next sh
For Each sh In ActiveWorkbook.Worksheets
sh.Protect Password:=PWORDL
Next sh
End Sub

Niddeh,

Modified you code to have the .cells commands done after you paste.

Option Explicit

Private Sub CommandButton1_Click()
Dim sh As Worksheet
Const PWORDO As String = "XXXX"
Const PWORDL As String = "XXXX1"
For Each sh In ActiveWorkbook.Worksheets
sh.Unprotect Password:=PWORDO
Next sh
Set sh = Sheets("Biography")
sh.Activate
With sh.Range("F13:K13,F16:K16,F19:K19")
..Locked = True
..FormulaHidden = False
sh.Range("F13:K13,F16:K16,F19:K19").Copy
End With
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> "Biography" Then
With sh
..Range("A1").PasteSpecial (xlPasteValues)
..Cells.Locked = False
..Cells.FormulaHidden = False
..Range("A100:C103").Locked = True
..Cells.FormulaHidden = False
End With
End If
Next sh
For Each sh In ActiveWorkbook.Worksheets
sh.Protect Password:=PWORDL
Next sh
End Sub

Mike


"Niddeh" wrote:

>
> So after comming here for advice I've now set up my workbook to leave
> some sheets protected with only certain areas unlocked to enter data
> in. Once data has been entered and the user clicks on a button sheets
> are then unprotected while the data entered is protected.
>
> What I am having a problem with now is copying the data entered and
> pasting only the values onto other sheets in the workbook.
>
> Basically what is supposed to happen is that once the Biography sheet
> is completed the macro should take the data from the ranges that users
> entered and copy this to all the other sheets in the workbook in a
> white font.
>
> Any help would be appreciated, the current code I'm using is as
> follows:
>
> Private Sub CommandButton1_Click()
> Dim sh As Worksheet
> Const PWORDO As String = "XXXX"
> Const PWORDL As String = "XXXX1"
> For Each sh In ActiveWorkbook.Worksheets
> sh.Unprotect Password:=PWORDO
> If sh.Name = "Biography" Then
> With sh.Range("F13:K13,F16:K16,F19:K19")
> .Locked = True
> .FormulaHidden = False
> Range("F13:K13,F16:K16,F19:K19").Select
> Range("F19").Activate
> Selection.Copy
> Sheets("Sheet1").Select
> End With
> Else
> With sh
> .Cells.Locked = False
> .Cells.FormulaHidden = False
> .Range("A100:C103").Locked = True
> .Cells.FormulaHidden = False
> .Select
> .Range("A1").Select
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> End With
> End If
> Next sh
> For Each sh In ActiveWorkbook.Worksheets
> sh.Protect Password:=PWORDL
> Next sh
> End Sub
>
>
> --
> Niddeh
> ------------------------------------------------------------------------
> Niddeh's Profile: http://www.excelforum.com/member.php...o&userid=36062
> View this thread: http://www.excelforum.com/showthread...hreadid=559969
>
>