Another basic problem

[Niddeh]

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

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

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

Any help would be appreciated, the current code I'm using is a
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 Su

 

[Guest]

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