I'm tasked with running an employee contest using Excel. I wrote code at
home using Excel 2000 and it works fine. At work, using 2003, I run into
problems. In the spreadsheet there are 3 sheets that must not be changed (a
scoring summary, a master, and a dummy entry form that is copied as an
entrant's specific sheet). There are 4 forms. Form1 gives instructions,
Form2 creates the contestants sheet and adds their name and statistics to the
summary sheet, Form3 provides additional instructions and populates Form4,
From4 gets input from the participant and adds the data to their personal
sheet. The code below works fine in 2000 (copying the "Entry 1" sheet,
adding it in the 4th tab slot, and changing the name of the sheet). In 2003,
it works fine the first time (for the 1st contestant). But after saving and
exiting the spreadsheet, the next contestant to open the spreadsheet will
encounter problems as the code copies and adds one of the first 2 sheets
(scoring summary or master) instead of Entry1 sheet. Any thoughts.... I'm
new to VB code

Private Sub CommandButton1_Click()
UserForm2.Hide 'Hides form2 after disclaimer
Dim Entrant As String
Entrant = InputBox("Please Enter Your Name" & Chr(13) & "First Name and
Last Initial" & Chr(13) & "(For Example; John P)", "Enter New Contestant")
Sheets("Entry1").Select 'Base sheet to be copied and renamed to
entrant name
Sheets("Entry1").Copy After:=Sheets(3) 'Place new sheet in 4th tab slot
Sheets("Entry1 (2)").Select 'Select the new sheet
Sheets("Entry1 (2)").Name = Entrant 'Rename sheet
Range("G2:J2").Select 'Select rage for name to be inserted into the form
ActiveCell.FormulaR1C1 = Entrant 'Place entrant name in cells
Sheets("Scoring Summary").Select
Rows("6:6").Select
Selection.Insert Shift:=xlDown 'insert row to add entrant summary data
Range("C5:P5").Select
Selection.Copy
Range("C6").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Replace What:="Entry1", Replacement:="'" & Entrant & "'",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Sheets(Entrant).Select
UserForm3.Show
End Sub

I could not determine exactly why you are getting the problem. However, when
you copy a worksheet, the new worksheet becomes the ActiveSheet so there is
no need to select it by it's new name; just use ActiveSheet. Try the
following and see if it is any better:-

UserForm2.Hide 'Hides form2 after disclaimer
Dim Entrant As String
Entrant = InputBox("Please Enter Your Name" & Chr(13) & _
"First Name and Last Initial" & Chr(13) & _
"(For Example; John P)", "Enter New Contestant")
Sheets("Entry1").Copy After:=Sheets(3) 'Place new sheet in 4th tab slot

ActiveSheet.Name = Entrant 'Rename sheet

Range("G2:J2").Select 'Select rage for name to be inserted into the form
ActiveCell.FormulaR1C1 = Entrant 'Place entrant name in cells
Sheets("Scoring Summary").Select
Rows("6:6").Select
Selection.Insert Shift:=xlDown 'insert row to add entrant summary data
Range("C5:P5").Select
Selection.Copy
Range("C6").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Replace What:="Entry1", Replacement:="'" & Entrant & "'",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Sheets(Entrant).Select
UserForm3.Show

--
Regards,

OssieMac


"Enginerd88" wrote:

I'm tasked with running an employee contest using Excel. I wrote code at
home using Excel 2000 and it works fine. At work, using 2003, I run into
problems. In the spreadsheet there are 3 sheets that must not be changed (a
scoring summary, a master, and a dummy entry form that is copied as an
entrant's specific sheet). There are 4 forms. Form1 gives instructions,
Form2 creates the contestants sheet and adds their name and statistics to the
summary sheet, Form3 provides additional instructions and populates Form4,
From4 gets input from the participant and adds the data to their personal
sheet. The code below works fine in 2000 (copying the "Entry 1" sheet,
adding it in the 4th tab slot, and changing the name of the sheet). In 2003,
it works fine the first time (for the 1st contestant). But after saving and
exiting the spreadsheet, the next contestant to open the spreadsheet will
encounter problems as the code copies and adds one of the first 2 sheets
(scoring summary or master) instead of Entry1 sheet. Any thoughts.... I'm
new to VB code

Private Sub CommandButton1_Click()
UserForm2.Hide 'Hides form2 after disclaimer
Dim Entrant As String
Entrant = InputBox("Please Enter Your Name" & Chr(13) & "First Name and
Last Initial" & Chr(13) & "(For Example; John P)", "Enter New Contestant")
Sheets("Entry1").Select 'Base sheet to be copied and renamed to
entrant name
Sheets("Entry1").Copy After:=Sheets(3) 'Place new sheet in 4th tab slot
Sheets("Entry1 (2)").Select 'Select the new sheet
Sheets("Entry1 (2)").Name = Entrant 'Rename sheet
Range("G2:J2").Select 'Select rage for name to be inserted into the form
ActiveCell.FormulaR1C1 = Entrant 'Place entrant name in cells
Sheets("Scoring Summary").Select
Rows("6:6").Select
Selection.Insert Shift:=xlDown 'insert row to add entrant summary data
Range("C5:P5").Select
Selection.Copy
Range("C6").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Replace What:="Entry1", Replacement:="'" & Entrant & "'",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
Sheets(Entrant).Select
UserForm3.Show
End Sub