Another basic problem
 

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