Changing name of worksheet based on data in cell of another worksheet
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? |
Changing name of worksheet based on data in cell of another worksheet
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? |
Changing name of worksheet based on data in cell of another worksheet
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? |
Changing name of worksheet based on data in cell of another worksheet
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. |
Changing name of worksheet based on data in cell of another worksheet
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 |
Changing name of worksheet based on data in cell of another worksheet
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? |
Changing name of worksheet based on data in cell of another worksheet
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 :-) |
Changing name of worksheet based on data in cell of another worksheet
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 |
Changing name of worksheet based on data in cell of another worksheet
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 |
Changing name of worksheet based on data in cell of another worksheet
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 |
Changing name of worksheet based on data in cell of another worksheet
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 |
Changing name of worksheet based on data in cell of another worksheet
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 |
Changing name of worksheet based on data in cell of another worksheet
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 |
Changing name of worksheet based on data in cell of another worksheet
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 |
Changing name of worksheet based on data in cell of another worksheet
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 |
Changing name of worksheet based on data in cell of another worksheet
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 |
Changing name of worksheet based on data in cell of another worksheet
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 |
Changing name of worksheet based on data in cell of another worksheet
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? |
Changing name of worksheet based on data in cell of another worksheet
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 |
Changing name of worksheet based on data in cell of another worksheet
"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 :-) |
Changing name of worksheet based on data in cell of another worksheet
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 |
Changing name of worksheet based on data in cell of another worksheet
"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 :-) |
Changing name of worksheet based on data in cell of another worksheet
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 |
Changing name of worksheet based on data in cell of another worksheet
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 |
All times are GMT +1. The time now is 09:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com