Matching sheet name to value in cell
 

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 Befo=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

Matching sheet name to value in cell
 

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

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

--
Regards,
Tom Ogilvy

"NathanG" wrote in message
...
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 Befo=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

Matching sheet name to value in cell
 

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" wrote in message
...
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 Befo=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