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?

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

On Apr 20, 3:11*pm, wrote:
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,

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?

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
InsertModule.

--
Garry

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

On Apr 20, 1:59*pm, GS wrote:
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
InsertModule.

--
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.

wrote :
On Apr 20, 1:59*pm, GS wrote:
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
InsertModule.

--
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.

That looks fine!

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

--
Garry

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

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
On Apr 20, 9:19*pm, " wrote:
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?

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

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

On Apr 21, 3:49*am, Paul Robinson
wrote:
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
On Apr 20, 9:19*pm, " wrote:



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?- Hide quoted text -

- 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

Clif McIrvin explained :
wrote in message
...
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.

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.

--
Garry

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

expressed precisely :
On Apr 21, 3:49*am, Paul Robinson
wrote:
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
On Apr 20, 9:19*pm, " wrote:



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?- Hide quoted text -

- 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

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

--
Garry

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

used his keyboard to write :
On Apr 21, 3:49*am, Paul Robinson
wrote:
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
On Apr 20, 9:19*pm, " wrote:



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?- Hide quoted text -

- 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

I forgot to include...

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

--
Garry

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

On Apr 21, 11:07*am, GS wrote:
used his keyboard to write :





On Apr 21, 3:49 am, Paul Robinson
wrote:
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
On Apr 20, 9:19 pm, " wrote:

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?- Hide quoted text -

- 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

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

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





On Thu, 21 Apr 2011 10:55:35 -0700 (PDT), "
wrote:

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

was thinking very hard :
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

Brad,
Thanks for the additional info. I've duplicated your scenario and ran
Paul's code 'as is' and it works fine for me. However, it could be
condensed somewhat to obviate the overhead of the unecessary
variables...

Sub ChangeTabNames2()
Dim rng As Range, sTemp As String, i As Integer
Application.ScreenUpdating = False
For Each rng In Worksheets("Sheet1").Range("B13:B30")
i = i + 1: sTemp = "Stud" & i
Sheets(sTemp).Name = sTemp & rng.Value
Next rng
End Sub

--
Garry

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

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.

--
Garry

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

On Apr 21, 12:02*pm, GS wrote:
was thinking very hard :

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

Brad,
Thanks for the additional info. I've duplicated your scenario and ran
Paul's code 'as is' and it works fine for me. However, it could be
condensed somewhat to obviate the overhead of the unecessary
variables...

Sub ChangeTabNames2()
* Dim rng As Range, sTemp As String, i As Integer
* Application.ScreenUpdating = False
* For Each rng In Worksheets("Sheet1").Range("B13:B30")
* * i = i + 1: sTemp = "Stud" & i
* * Sheets(sTemp).Name = sTemp & rng.Value
* Next rng
End Sub

--
Garry

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

Garry,

We have success! However, it appends the name vice replacing it. Oh,
I used your revised code from the previous post. We are so close, and
you have been soo helpful. I was wondering if you could help me just
once more?

Brad

formulated the question :
On Apr 21, 12:02*pm, GS wrote:
was thinking very hard :

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

Brad,
Thanks for the additional info. I've duplicated your scenario and ran
Paul's code 'as is' and it works fine for me. However, it could be
condensed somewhat to obviate the overhead of the unecessary
variables...

Sub ChangeTabNames2()
* Dim rng As Range, sTemp As String, i As Integer
* Application.ScreenUpdating = False
* For Each rng In Worksheets("Sheet1").Range("B13:B30")
* * i = i + 1: sTemp = "Stud" & i
* * Sheets(sTemp).Name = sTemp & rng.Value
* Next rng
End Sub

--
Garry

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

Garry,

We have success! However, it appends the name vice replacing it. Oh,
I used your revised code from the previous post. We are so close, and
you have been soo helpful. I was wondering if you could help me just
once more?

Brad

Yeah, sorry about that! I was just appending a letter to each existing
name so I could test it. Change this line...

Sheets(sTemp).Name = sTemp & rng.Value

to...

Sheets(sTemp).Name = rng.Value

--
Garry

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

On Apr 21, 12:02*pm, GS wrote:
was thinking very hard :

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

Brad,
Thanks for the additional info. I've duplicated your scenario and ran
Paul's code 'as is' and it works fine for me. However, it could be
condensed somewhat to obviate the overhead of the unecessary
variables...

Sub ChangeTabNames2()
* Dim rng As Range, sTemp As String, i As Integer
* Application.ScreenUpdating = False
* For Each rng In Worksheets("Sheet1").Range("B13:B30")
* * i = i + 1: sTemp = "Stud" & i
* * Sheets(sTemp).Name = sTemp & rng.Value
* Next rng
End Sub

--
Garry

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

Gentlemen,

We have success again! I looked at the first bit of code that was
given to me and relized that after the first try of getting it too
work, it had changed the name of the first worksheet. After that it
wasn't working because it was looking for a sheet named "Stud1" and
couldnt find it. I renamed it back to Stud1 and ran the Macro and it
work perfectly. However, the second bit of code:

Sub ChangeTabNames2()
Dim rng As Range, sTemp As String, i As Integer
Application.ScreenUpdating = False
For Each rng In Worksheets("Sheet1").Range("B13:B30")
i = i + 1: sTemp = "Stud" & i
Sheets(sTemp).Name = sTemp & rng.Value
Next rng
End Sub

Does work, but appends the end of the name giving me a worksheet name
that looks like "Stud1Jones". Regardless it is working now. I want
to thank all of you for your help. Oh, is there a way to undo it
without having to manually change the worksheet names back to Stud1
through Stud18?

presented the following explanation :
On Apr 21, 12:02*pm, GS wrote:
was thinking very hard :

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

Brad,
Thanks for the additional info. I've duplicated your scenario and ran
Paul's code 'as is' and it works fine for me. However, it could be
condensed somewhat to obviate the overhead of the unecessary
variables...

Sub ChangeTabNames2()
* Dim rng As Range, sTemp As String, i As Integer
* Application.ScreenUpdating = False
* For Each rng In Worksheets("Sheet1").Range("B13:B30")
* * i = i + 1: sTemp = "Stud" & i
* * Sheets(sTemp).Name = sTemp & rng.Value
* Next rng
End Sub

--
Garry

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

Gentlemen,

We have success again! I looked at the first bit of code that was
given to me and relized that after the first try of getting it too
work, it had changed the name of the first worksheet. After that it
wasn't working because it was looking for a sheet named "Stud1" and
couldnt find it. I renamed it back to Stud1 and ran the Macro and it
work perfectly. However, the second bit of code:

Sub ChangeTabNames2()
Dim rng As Range, sTemp As String, i As Integer
Application.ScreenUpdating = False
For Each rng In Worksheets("Sheet1").Range("B13:B30")
i = i + 1: sTemp = "Stud" & i
Sheets(sTemp).Name = sTemp & rng.Value
Next rng
End Sub

Does work, but appends the end of the name giving me a worksheet name
that looks like "Stud1Jones". Regardless it is working now. I want
to thank all of you for your help. Oh, is there a way to undo it
without having to manually change the worksheet names back to Stud1
through Stud18?

Read my previous post!

--
Garry

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

"GS" wrote in message
...
presented the following explanation :
On Apr 21, 12:02 pm, GS wrote:
was thinking very hard :

[ ]

Sub ChangeTabNames2()
Dim rng As Range, sTemp As String, i As Integer
Application.ScreenUpdating = False
For Each rng In Worksheets("Sheet1").Range("B13:B30")
i = i + 1: sTemp = "Stud" & i
Sheets(sTemp).Name = sTemp & rng.Value
Next rng
End Sub

Does work, but appends the end of the name giving me a worksheet name
that looks like "Stud1Jones". Regardless it is working now. I want
to thank all of you for your help.

Oh, is there a way to undo it
without having to manually change the worksheet names back to Stud1
through Stud18?


If the master sheet is always the first sheet, and there are always 18
student sheets, this should rename the student sheets as Stud1 through
Stud18:

Sub ChangeTabNames3()
Dim sTemp As String, i As Integer
Application.ScreenUpdating = False
For i = 1 To 18
sTemp = "Stud" & i
Sheets(i + 1).Name = sTemp
Next i
End Sub

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

Clif McIrvin formulated the question :
"GS" wrote in message ...
presented the following explanation :
On Apr 21, 12:02 pm, GS wrote:
was thinking very hard :

[ ]

Sub ChangeTabNames2()
Dim rng As Range, sTemp As String, i As Integer
Application.ScreenUpdating = False
For Each rng In Worksheets("Sheet1").Range("B13:B30")
i = i + 1: sTemp = "Stud" & i
Sheets(sTemp).Name = sTemp & rng.Value
Next rng
End Sub

Does work, but appends the end of the name giving me a worksheet name
that looks like "Stud1Jones". Regardless it is working now. I want
to thank all of you for your help.

Oh, is there a way to undo it
without having to manually change the worksheet names back to Stud1
through Stud18?


If the master sheet is always the first sheet, and there are always 18
student sheets, this should rename the student sheets as Stud1 through
Stud18:

Sub ChangeTabNames3()
Dim sTemp As String, i As Integer
Application.ScreenUpdating = False
For i = 1 To 18
sTemp = "Stud" & i
Sheets(i + 1).Name = sTemp
Next i
End Sub

Clif,
I think he's got it working if he catches up to the replies. Not sure
how he's accessing this NG but he posted the same problem AFTER I
posted corrected code.

--
Garry

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

"GS" wrote in message
...
Clif McIrvin formulated the question :
"GS" wrote in message
...
presented the following explanation :
On Apr 21, 12:02 pm, GS wrote:
was thinking very hard :

[ ]

Sub ChangeTabNames2()
Dim rng As Range, sTemp As String, i As Integer
Application.ScreenUpdating = False
For Each rng In Worksheets("Sheet1").Range("B13:B30")
i = i + 1: sTemp = "Stud" & i
Sheets(sTemp).Name = sTemp & rng.Value
Next rng
End Sub

Does work, but appends the end of the name giving me a worksheet
name
that looks like "Stud1Jones". Regardless it is working now. I
want
to thank all of you for your help.

Oh, is there a way to undo it
without having to manually change the worksheet names back to Stud1
through Stud18?


If the master sheet is always the first sheet, and there are always
18 student sheets, this should rename the student sheets as Stud1
through Stud18:

Sub ChangeTabNames3()
Dim sTemp As String, i As Integer
Application.ScreenUpdating = False
For i = 1 To 18
sTemp = "Stud" & i
Sheets(i + 1).Name = sTemp
Next i
End Sub

Clif,
I think he's got it working if he catches up to the replies. Not sure
how he's accessing this NG but he posted the same problem AFTER I
posted corrected code.


Yeah, I caught that. I posted to answer the second part of his question
.... the "undo" part.

Looks like he's using Google groups; I don't know how reliably gg
threads current discussions.

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

Here's a modified sub that fits your scenario with requiring editing...

Sub ChangeTabNames2()
Dim rng As Range, i As Integer
Application.ScreenUpdating = False
For Each rng In Worksheets("Master").Range("B13:B30")
i = i + 1: Sheets("Stud" & i).Name = rng.Value
Next rng
End Sub

--
Garry

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

GS presented the following explanation :
Here's a modified sub that fits your scenario with requiring editing...

This line should read...

Here's a modified sub that fits your scenario without requiring any
editing...

--
Garry

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