Use cell value as sheet name (2003)

[timelord]

My boss changed his mind. instead of creating a new file he wants a new sheet with the name from one of the cells created. I managed to get Excel to create the new sheet, copy over all the info from the old sheet, delete the info entered on the old sheet, but cannot get the name of the new sheet to equal Range(D3:E3). (these are merged cells)

[HansV]

You must refer to the first cell in a merged range, so use Range("D3").

[timelord]

Right, now that is working but I cannot copy sheet1 to the new sheet because the name is a variable. I put in:

Sheets("D3").Select

D3 is the cell that I grabbed to name the new sheet. Once I select that sheet I want to paste all the information from Sheet1. Here is the total of my script so far:

Sheets("Grading").Select
Sheets.Add.Name = Range("D3")
Sheets("Grading").Select
Cells.Select
Selection.Copy
Sheets("D3").Select
ActiveSheet.Paste

[sdckapr]

Sheets("D3").Select

is trying to select a sheet literally named "D3". If you want to select the sheet that is named what the value in RAnge D3 is, try:
Sheets(Range("D3").value).Select

Does this code do what you want (warning "aircode") without all the selecting?

dim wGrade as worksheet
dim wks as worksheet
set wGrade = Sheets("Grading")
set wks = Sheets.Add
wks.Name = wgrade.Range("D3").value
wgrade.Cells.Copy wks.range("A1")


Steve

[timelord]

Steve,
That worked great! a little fiddling with the rest of my macro and all is almost running smoothly. Just one more tiny issue. This sheet is setup as protected so that users can tab from one entry field to another, but I want to change the order of the tabbed fields. Any thoughts?

Doug

[timelord]

After the user fills in the last field they should then hit 'Tab' and be resting on the 'Submit' button. That would be perfect, but does not have to happen that way.

Doug

[sdckapr]

I don't think there is a way to do this in excel directly:

You could put the cells themselves in the proper order so the tabs go as desired

Instead of editing in the worksheet "proper", you could create a userform for data entry and set the objects in the form as desired. When the user wants to add/edit/delete anything it would go thru the userform...

Steve

[sdckapr]

1)Add the submit button
Add unprotected cells next in the sequence
You could have a selectionchange event to "trap" when this unprotected cells (or cells) is selected and have the code put the focus on the submit button

2) as mentioned earlier, you could add a userform...

Steve

[timelord]

Thanks gang! Everything is working to perfection! (or close enough for government work anyway)