Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing name of worksheet based on data in cell of another worksheet
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 :-) |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing Cell Color based upon value on another worksheet | Excel Programming | |||
Return filtered values into report worksheet based on filtered valueon the data worksheet | Excel Worksheet Functions | |||
Select correct worksheet based on data entered into a cell | Excel Worksheet Functions | |||
Select correct worksheet based on data entered into a cell | Excel Programming | |||
Changing cell values based on a worksheet name | Excel Programming |