Changing name of worksheet based on data in cell of another worksheet

[bss5974]

I have an excel workbook, with a "master" worksheet for data input and
have 18 other worksheets titled Stud1 through Stud18. I am trying to
have the names of the "Stud" worksheets automatically change to the
student's lastnames on the "master" worksheet (there is a column on
the master worksheet named "lastname" from B13 to B30. The lastname
entered in B13 should be the name of the "Stud1" worksheet and B14 the
name of "Stud14" worksheet, and so on.) I have little to no VBA
experience and would love some help with this. Anyone out there have
any ideas?

 

[Paul Robinson]

Hi
try this

Sub ChangeTabNames()
Dim myNames As Range, mycell As Range
Dim Temp As String, i As Integer

Application.Screenupdating = False

Set myNames = Worksheets("Master").Range("B13:B30")
i = 1
For Each mycell In myNames
Temp = "Stud" & i
Worksheets(Temp).Name = mycell.Value
i = i + 1
Next mycell
End Sub

If using excel 2003 go to Tools, Macro, Visual Basic Editor. In the
toolbar you get go to Insert then Module. Paste the above code into
here. You can now close the VBE. Back in Excel proper go to Tools,
Macro, Macros.. and run the macro. It doesnt matter which sheet is
visible. Try it out on a copy of your worksheet first to test it is
OK!
If using Excel 2007 (2010?) click the round Home button and choose
Excel Options. Click on the Developer Tab option. Back in Excel proper
click on the Developer Tab and bring up the editor etc.

regards
Paul

 

[bss5974]

Paul,

Thanks for the quick response. I copied the code you replied with
into the VBA editor and place it on the Master Sheet object. I saved
and then started to enter lastnames from B13 to B30. The names of the
Stud1 - Stud18 worksheets didn't change. Was there a part of your
code that I needed to edit or customize for my workbook? Oh, I am
using Excel 2007. Any suggestions?

 

[GS]

(e-mail address removed) formulated on Wednesday :

Paul,

Thanks for the quick response. I copied the code you replied with
into the VBA editor and place it on the Master Sheet object. I saved
and then started to enter lastnames from B13 to B30. The names of the
Stud1 - Stud18 worksheets didn't change. Was there a part of your
code that I needed to edit or customize for my workbook? Oh, I am
using Excel 2007. Any suggestions?

Post the revised code!
Also, put/move the code into a standard module. To create a standard
module, right-click the project in the Project Explorer and choose
Insert>Module.

 

[bss5974]

Post the revised code!
Also, put/move the code into a standard module. To create a standard
module, right-click the project in the Project Explorer and choose
Insert>Module.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Garry,

In my initial description of the problem I made a mistake. It should
have read like this...
"The lastname in Cell B13 of the Master worksheet is for the Stud1
worksheet, and B14 for Stud2 and so on."

My revised code was this:

Sub ChangeTabNames()
Dim myNames As Range, mycell As Range
Dim Temp As String, i As Integer


Application.ScreenUpdating = False


Set myNames = Worksheets("Input Sheet").Range("B13:B30")
i = 1
For Each mycell In myNames
Temp = "Stud" & i
Worksheets(Temp).Name = mycell.Value
i = i + 1
Next mycell
End Sub

The only thing I changed from the originally posted code was the name
of the "Master" to "Input Sheet". Other than that, it is exactly as
posted earlier from Paul.

 

[GS]

Garry,

In my initial description of the problem I made a mistake. It should
have read like this...
"The lastname in Cell B13 of the Master worksheet is for the Stud1
worksheet, and B14 for Stud2 and so on."

My revised code was this:

Sub ChangeTabNames()
Dim myNames As Range, mycell As Range
Dim Temp As String, i As Integer


Application.ScreenUpdating = False


Set myNames = Worksheets("Input Sheet").Range("B13:B30")
i = 1
For Each mycell In myNames
Temp = "Stud" & i
Worksheets(Temp).Name = mycell.Value
i = i + 1
Next mycell
End Sub

The only thing I changed from the originally posted code was the name
of the "Master" to "Input Sheet". Other than that, it is exactly as
posted earlier from Paul.

That looks fine!

So.., did you move the code to a standard module so it's accessible in
the Macros dialog?

 

[Paul Robinson]

Hi
You have put the code in the wrong place (re-read my original
reply). The worksheet module is for special kinds of procedures
called worksheet level procedures and respond to certain things you do
to the worksheet. You need to put the code into what is called a
general code module. In the VBE go to Insert on the toolbar then click
on Module. You will get a blank white screen. Paste the code into
there (and delete it from the worksheet module!).
regards
Paul

 

[Clif McIrvin]

Paul,

Thanks for the quick response. I copied the code you replied with
into the VBA editor and place it on the Master Sheet object. I saved
and then started to enter lastnames from B13 to B30. The names of the
Stud1 - Stud18 worksheets didn't change. Was there a part of your
code that I needed to edit or customize for my workbook? Oh, I am
using Excel 2007. Any suggestions?

Sounds like you want the sheet name change to happen "automagically"
whenever you change the student last names on your master sheet.

What Paul gave you is macro code that you have to invoke for it to run
(he told you how in his original post.) There are very good reasons for
preferring a macro that the user must invoke over using magic code ....
not the least of which is that (often) the user of the worksheet knows
nothing of the magic, and when something breaks it sometime in the
future it can be a nightmare to find and fix. Heck -- I forget about the
magic that I write into my own worksheets that will never be used by
anyone else. (Never is a looong time!)

Will you ever want to change a sheet name a second (or third, etc.)
time? (For instance, if the name was typed incorrectly and needs to be
changed, or if a student has a legal name change.) If you ever want to
change a sheet that is NOT named Stud1 - Stud18 Paul's code will not
handle that.

The macro could be converted to a worksheet change event procedure to
make it "automagic," but it would still only work to rename a sheet
named Stud1 - Stud18.

If you want the code to also handle future name changes this becomes a
problem with a much more involved solution.

 

[bss5974]

Hi
You have put the code in the wrong place (re-read my original
reply). The worksheet module is for special kinds of procedures
called worksheet level procedures and respond to certain things you do
to the worksheet. You need to put the code into what is called a
general code module. In the VBE go to Insert on the toolbar then click
on Module. You will get a blank white screen. Paste the code into
there (and delete it from the worksheet module!).
regards
Paul



- Show quoted text -

Gentlemen,

I moved the code into it's own module and saved the workbook.
I then ran the Macro and recieved a "run-time error '9' Subscript out
of range"
I hit the "Debug" option and the VBE had the following line of code
highlighted:

Worksheets(Temp).Name = mycell.Value

Is there something wrong? Again I appreciate all the help everyone
has put forth!

Brad

 

[GS]

Clif McIrvin explained :

Sounds like you want the sheet name change to happen "automagically" whenever
you change the student last names on your master sheet.

What Paul gave you is macro code that you have to invoke for it to run (he
told you how in his original post.) There are very good reasons for
preferring a macro that the user must invoke over using magic code .... not
the least of which is that (often) the user of the worksheet knows nothing of
the magic, and when something breaks it sometime in the future it can be a
nightmare to find and fix. Heck -- I forget about the magic that I write into
my own worksheets that will never be used by anyone else. (Never is a looong
time!)

Will you ever want to change a sheet name a second (or third, etc.) time?
(For instance, if the name was typed incorrectly and needs to be changed, or
if a student has a legal name change.) If you ever want to change a sheet
that is NOT named Stud1 - Stud18 Paul's code will not handle that.

The macro could be converted to a worksheet change event procedure to make it
"automagic," but it would still only work to rename a sheet named Stud1 -
Stud18.

If you want the code to also handle future name changes this becomes a
problem with a much more involved solution.

I'm thinking that these sheets pre-exist and so is why the rename is
required. I'd be more inclined to just copy a 'master' sheet for each
student and rename it as I do that.

I'm curious why a separate sheet for each student> Normally, student
score data uses 1 sheet per class/course and contains a list of all
students in that class/course. Subject details are then stored in
columns, followed by total/final grade scores/averages. IOW, if the
only difference on the sheets will be the student name then 1 sheet
would suffice regardless of subject detail.

 

[GS]

(e-mail address removed) expressed precisely :

Gentlemen,

I moved the code into it's own module and saved the workbook.
I then ran the Macro and recieved a "run-time error '9' Subscript out
of range"
I hit the "Debug" option and the VBE had the following line of code
highlighted:

Worksheets(Temp).Name = mycell.Value

Is there something wrong? Again I appreciate all the help everyone
has put forth!

Brad

Is there a name missing in the list, maybe? Are you sure the list is
rows 13 through 30?

 

[GS]

(e-mail address removed) used his keyboard to write :

Gentlemen,

I moved the code into it's own module and saved the workbook.
I then ran the Macro and recieved a "run-time error '9' Subscript out
of range"
I hit the "Debug" option and the VBE had the following line of code
highlighted:

Worksheets(Temp).Name = mycell.Value

Is there something wrong? Again I appreciate all the help everyone
has put forth!

Brad

I forgot to include...

Are there 18 worksheets named "Stud1" through "Stud18"?

 

[bss5974]

(e-mail address removed) used his keyboard to write :









I forgot to include...

Are there 18 worksheets named "Stud1" through "Stud18"?

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

Garry:

I am trying to make this process as easy for my intructors as possible
and yes there is a need to have a sheet for each student. Having the
worksheets name change automatically was just an idea I had to help
make the process as automated as possible. The intent is to have the
Class Commanders of each class only have to enter the names in once
(on the "input sheet"). I have verified that the cells on the "input
sheet" are cells B13 through B30 and yes there are 18 different
worksheets named Stud1 through Stud18. Hope that helps.

Brad

 

[Gord Dibben]

Brad

Just to experiment and get a feel for what Paul's code does..............

Open a NEW workbook.

Delete all but one sheet...............name that sheet "Master"

Copy these two macros to a module in that workbook.

Sub Add_Sheets()
For i = 18 To 1 Step -1
Worksheets.Add.Name = "Stud" & i
Next
End Sub

Sub ChangeTabNames()
Dim myNames As Range, mycell As Range
Dim Temp As String, i As Integer

Application.ScreenUpdating = False

Set myNames = Worksheets("Master").Range("B13:B30")
i = 1
For Each mycell In myNames
Temp = "Stud" & i
Worksheets(Temp).Name = mycell.Value
i = i + 1
Next mycell
End Sub

Save workbook.

Run Add_Sheets macro to get 18 new sheets named Stud1 to Stud18

Enter a list of names in Master sheet at B13:B30

Save workbook.

Run Paul's ChangeTabNames macro.

What occurs?

Do you get the name changes?

Do you now want this to fully automated as Cliff suggests?


Gord Dibben MS Excel MVP

 

[GS]

Hi Gord,
I did essentially the same thing (without saving anything) and got the
expected results using Paul's code 'as is', saving for editing for the
sheetname containing the list.

I revised the code a bit but it doesn't do anything differently (I
simply removed unecessary vars).

Not sure why Brad can't get it working. The error will result when the
original sheetname is not '"Stud" & i'. I'm thinking he's trying to
rerun the code and one of the sheetnames has changed from a previous
attempt.