Use cell value as sheet name (2003)

Topic

New Reply

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)

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote