Excel Worksheet Tabs

[Frustrated Instructor]

I have created a grade book for the classes I teach at my local university. I
have been able to use many of the formula functions, etc. to automate much of
the calculation and compilation work I need to do to get grades out. I have a
tab for each student in a class. These worksheets auto populate the student's
information when I copy the list into the first worksheet.

With that as background, what I would like to have happen is for the tabs to
also auto populate the student's name on the tab, as each worksheet is an
individual student. Is there a way to set this up?

Don Goss
Frustrated Instructor

 

[Gord Dibben]

With a list of student names in first sheet in A1:A40

Sub NameWS()
'name sheets with list in Column A on first sheet
On Error Resume Next
For I = 2 To Worksheets.Count
Sheets(I).Name = Sheets(1).Cells(I, 1).Value
Next I
End Sub


Gord Dibben MS Excel MVP

On Fri, 1 Aug 2008 07:01:01 -0700, Frustrated Instructor <Frustrated

 

[Frustrated Instructor]

Gord, thank you for the response. I need to plead ignorant when it comes to
writing code. Do I place the information as a macro, or do I use it as a VBA
command? Additionally, do I create the information as a cell on the first
worksheet? Finally, can the list of names be in Column B?

I appreciate the help

Frustrated instructor

 

[Gord Dibben]

It is a macro and needs nothing more than copying to a module to be run.

See below for the How-to on storing and running.

The student's names would be in Column B from B1 to wherever in Column B on
your first worksheet.

Say B1:B40

Here is revised code......................note the Cells(I, 2) denotes
column(2) which is B

Sub NameWS()
'name sheets with list in Column B on first sheet
On Error Resume Next
For I = 2 To Worksheets.Count
Sheets(I).Name = Sheets(1).Cells(I, 2).Value
Next I
End Sub

If you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord