Matching sheet name to value in cell

[NathanG]

I am working on a system where the user can select a job and go to the sheet
where the details of that job are held. I have disabled the option to view
the sheets due to GUI design. I cannot change the sheet name to equal the job
name entered as text on a sheet . Below is the current code

Sheets("NewJob").Select
Sheets("NewJob").Copy Before:=Sheets(1) ' copying sheet "NewJob" The
copied sheet becomes NewJob (2)
ActiveWorkbook.Unprotect
ActiveSheet.Shapes("CmdSaveJob").Select
Selection.Delete 'deleting an object
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.Range("C8:E8").Select ' where the name of the job is
Selection.Copy
Application.Run "Prototype2.xls!Addjob"

Sheets("Job").Select
Range("C29").Select
ActiveSheet.Paste ' copying name of the job to another sheet to populate
a combo box using data validation and a list
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown
Range("B10").Select

Sheets("NewJob").Select
ActiveSheet.Range("C8:E8") = ""
ActiveSheet.Range("C10:d10") = ""
ActiveSheet.Range("C12") = ""
ActiveSheet.Shapes("Comments").Select
Selection.Characters.Text = ""
ActiveWorkbook.Save

Each time a sheet is copied the new sheet is named "New Job (2)" or (3) etc.
I either want a method of matching the job name on the sheet to the sheetname
or a method of changing the sheetname to the job name.

Any suggestions??? Would greatly appreciate it.

Nathan

[Tom Ogilvy]

Activesheet.Name = ActiveSheet.Range("C29").Value

Change Range("C29") to reflect where the name is to be found.

--
Regards,
Tom Ogilvy

"NathanG" <NathanG@discussions.microsoft.com> wrote in message
news:3207F1D7-00A9-43F0-8973-70976E0D2630@microsoft.com...
> I am working on a system where the user can select a job and go to the
sheet
> where the details of that job are held. I have disabled the option to view
> the sheets due to GUI design. I cannot change the sheet name to equal the
job
> name entered as text on a sheet . Below is the current code
>
> Sheets("NewJob").Select
> Sheets("NewJob").Copy Before:=Sheets(1) ' copying sheet "NewJob" The
> copied sheet becomes NewJob (2)
> ActiveWorkbook.Unprotect
> ActiveSheet.Shapes("CmdSaveJob").Select
> Selection.Delete 'deleting an object
> ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
> ActiveSheet.Range("C8:E8").Select ' where the name of the job is
> Selection.Copy
> Application.Run "Prototype2.xls!Addjob"
>
> Sheets("Job").Select
> Range("C29").Select
> ActiveSheet.Paste ' copying name of the job to another sheet to
populate
> a combo box using data validation and a list
> Application.CutCopyMode = False
> Selection.Insert Shift:=xlDown
> Range("B10").Select
>
> Sheets("NewJob").Select
> ActiveSheet.Range("C8:E8") = ""
> ActiveSheet.Range("C10:d10") = ""
> ActiveSheet.Range("C12") = ""
> ActiveSheet.Shapes("Comments").Select
> Selection.Characters.Text = ""
> ActiveWorkbook.Save
>
> Each time a sheet is copied the new sheet is named "New Job (2)" or (3)
etc.
> I either want a method of matching the job name on the sheet to the
sheetname
> or a method of changing the sheetname to the job name.
>
> Any suggestions??? Would greatly appreciate it.
>
> Nathan

[NathanG]

Thank you. That worked.

Kind regards,

Nathan
"Tom Ogilvy" wrote:

> Activesheet.Name = ActiveSheet.Range("C29").Value
>
> Change Range("C29") to reflect where the name is to be found.
>
> --
> Regards,
> Tom Ogilvy
>
> "NathanG" <NathanG@discussions.microsoft.com> wrote in message
> news:3207F1D7-00A9-43F0-8973-70976E0D2630@microsoft.com...
> > I am working on a system where the user can select a job and go to the
> sheet
> > where the details of that job are held. I have disabled the option to view
> > the sheets due to GUI design. I cannot change the sheet name to equal the
> job
> > name entered as text on a sheet . Below is the current code
> >
> > Sheets("NewJob").Select
> > Sheets("NewJob").Copy Before:=Sheets(1) ' copying sheet "NewJob" The
> > copied sheet becomes NewJob (2)
> > ActiveWorkbook.Unprotect
> > ActiveSheet.Shapes("CmdSaveJob").Select
> > Selection.Delete 'deleting an object
> > ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
> Scenarios:=True
> > ActiveSheet.Range("C8:E8").Select ' where the name of the job is
> > Selection.Copy
> > Application.Run "Prototype2.xls!Addjob"
> >
> > Sheets("Job").Select
> > Range("C29").Select
> > ActiveSheet.Paste ' copying name of the job to another sheet to
> populate
> > a combo box using data validation and a list
> > Application.CutCopyMode = False
> > Selection.Insert Shift:=xlDown
> > Range("B10").Select
> >
> > Sheets("NewJob").Select
> > ActiveSheet.Range("C8:E8") = ""
> > ActiveSheet.Range("C10:d10") = ""
> > ActiveSheet.Range("C12") = ""
> > ActiveSheet.Shapes("Comments").Select
> > Selection.Characters.Text = ""
> > ActiveWorkbook.Save
> >
> > Each time a sheet is copied the new sheet is named "New Job (2)" or (3)
> etc.
> > I either want a method of matching the job name on the sheet to the
> sheetname
> > or a method of changing the sheetname to the job name.
> >
> > Any suggestions??? Would greatly appreciate it.
> >
> > Nathan
>
>
>