Code creates a new copy of an existing timesheet(sheet), prompts the user
for pay rates (x2), then names the sheet the employee name, then places a
copy of the name on the Enter - Exit sheet for viewing all employee names.

Code i am using:

Protect code that errors code is:


Sub ProtectActive()
'
' Macro2 Macro
' Macro recorded 24-11-2006 by Corey
'
ThisWorkbook.ActiveSheet.Protect Password:="1234" ' <==== Active Sheet due
to sheet name or number not known until sheet is created.
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub







Actual code that runs then calls the macro to protect below. (Arrow
indicates where protect macro is called/error occurs):

Sub InputNewName()
Call Unprotect
Call UnprotectJC
' ADD New Employee TimeSheet Here
res = InputBox("Please Enter the Name for the New Employee", "....")
If res = "" Then
Exit Sub
Else
If res = Range("B20").Text Then
GoTo cc
End If
If res = Range("B18").Text Then
GoTo cc
End If
If res = Range("B16").Text Then
GoTo cc
End If
If res = Range("B14").Text Then
GoTo cc
End If
If res = Range("B12").Text Then
GoTo cc
End If
If res = Range("F20").Text Then
GoTo cc
End If
If res = Range("F18").Text Then
GoTo cc
End If
If res = Range("F16").Text Then
GoTo cc
End If
If res = Range("F16").Text Then
GoTo cc
End If
If res = Range("F14").Text Then
GoTo cc
End If
If res = Range("F12").Text Then
GoTo cc
End If
If res = Range("I20").Text Then
GoTo cc
End If
If res = Range("I18").Text Then
GoTo cc
End If
If res = Range("I16").Text Then
GoTo cc
End If
If res = Range("I14").Text Then
GoTo cc
End If
If res = Range("I12").Text Then
GoTo cc
End If
Workbooks("TimeSheets").Activate
Sheets("JC").Select
Worksheets("JC").Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = res ' Name the TimeSheet the Value in the Input Box
Range("K2").Value = res
Application.CutCopyMode = False
Range("X3").ClearContents
Range("X6").ClearContents
Range("A1").Select
res = InputBox("What is the NORMAL Hourly Rate of Pay to be Set
At(Inclusive of Casual Loading) ?", " ....")
If res = "" Then
MsgBox "There MUST be a Value Placed in Cell(X3) Before the 1st
Payweek.", , "...."
GoTo here
Else
Range("X3").Value = res
End If
res = InputBox("What is the MINE Hourly rate of Pay to be set at ? ",
"....")
If res = "" Then
MsgBox "There MUST be a Value Placed in Cell(X6) Before the 1st
Payweek.", , "...."
Else
Range("X6").Value = res
End If
Application.DisplayAlerts = False
Call ClearTimeSheetValues
End If
he
Range("K2").Copy
'Call ProtectActive <================= Want to Protect the sheet before
leaving to (Enter - Exit) Sheet, But ERROR occurs due to next pasting steps
cannot be done while sheet is protected.
Sheets("Enter - Exit").Select
With Sheets("Enter - Exit")
Select Case True
Case .Range("I14").Value = ""
.Range("I14").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sheets("Enter - Exit").Select
Case .Range("I16").Value = ""
.Range("I16").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sheets("Enter - Exit").Select
Case .Range("I18").Value = ""
.Range("I18").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sheets("Enter - Exit").Select
Case .Range("B20").Value = ""
.Range("B20").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sheets("Enter - Exit").Select
Case .Range("F20").Value = ""
.Range("F20").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sheets("Enter - Exit").Select
Case .Range("I20").Value = ""
.Range("I20").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sheets("Enter - Exit").Select
GoTo bb
'Do nothing
MsgBox "Please Contact Corey, to Add More Places to Link the New
TimeSheet to.", , "...."
Application.DisplayAlerts = True
Call Protect
Exit Sub
End Select
End With
If res = "" Then
Application.DisplayAlerts = False
With ActiveSheet
..Select
..Delete
GoTo bb
End With
Sheets("Enter - Exit").Select
Application.CutCopyMode = False
GoTo bb
Else
'ActiveSheet.Name = res ' Name the TimeSheet the Value in the Input Box
'Range("K2").Value = res
End If
Application.DisplayAlerts = True
GoTo bb
bb:
Sheets("Enter - Exit").Select
Call Protect
Exit Sub
cc:
MsgBox "ALERT...." & vbCrLf & vbTab & "The Employee Name Already Exits." &
vbCrLf & vbCrLf & vbTab & "Please Check the Names on the Main Page.", ,
"...."
Sheets("Enter - Exit").Select
Call Protect
Call ProtectJC
End Sub

Question is then, how can i protect the sheet just created, as i cannot use
a name for the sheet as it is not named until the sheet is created?
Reason i had macro protectactive refer to the activesheet.

Or
Is there a way to allow the pasting of the sheet name into the Enter - Exit
sheet after the sheet was protected still ?


Any assistance appreciated.

Corey....