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
>
|
Similar Threads
