Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Basic format problem | Excel Discussion (Misc queries) | |||
basic txt import problem | Excel Discussion (Misc queries) | |||
Yet another basic problem | Excel Programming | |||
Basic Solver Problem | Excel Worksheet Functions | |||
Visual Basic problem | Excel Programming |