name each sheet from a list on sheet 1
 

I am new to excel but have to set the following up:
I have a front sheet with student names on it. Then I have up to 44 sheets
behind this. I just want to be able to somehow get those names onto the
sheet tab at the bottom of the page in the same order as on the list then use
this as a template.
So at the top of the list I have say Fred Bloggs: I need him to have his
name on the tab at the bottom instead of it saying "sheet 2" and then second
on list as name on bottom of third sheet tab etc. The data on the sheets
that refer to sheet one has already been set up but I have to do this 24
times and I'm sure there must be a quicker way than typing it in by hand each
time. Is there? Thank you very much.

name each sheet from a list on sheet 1
 

Hi,

Right click any sheet tab, view code and paste this in and run it. The code
assumes the names are in Sheet 1 A1 down,

Sub name_Sheets()
On Error Resume Next
For x = 2 To Worksheets.Count
Sheets(x).Name = Sheets("Sheet1").Cells(x - 1, 1).Value
Next
End Sub


Mike

"Flumoxed" wrote:

I am new to excel but have to set the following up:
I have a front sheet with student names on it. Then I have up to 44 sheets
behind this. I just want to be able to somehow get those names onto the
sheet tab at the bottom of the page in the same order as on the list then use
this as a template.
So at the top of the list I have say Fred Bloggs: I need him to have his
name on the tab at the bottom instead of it saying "sheet 2" and then second
on list as name on bottom of third sheet tab etc. The data on the sheets
that refer to sheet one has already been set up but I have to do this 24
times and I'm sure there must be a quicker way than typing it in by hand each
time. Is there? Thank you very much.

name each sheet from a list on sheet 1
 

Put the names in column A of the first sheet.

The first sheet will NOT be renamed.

Option Explicit
Sub sheetnamer()
Dim i As Long, n As Long, j As Long, shetcnt As Long
shetcnt = Sheets.Count
Sheets(1).Activate
n = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To n
j = i + 1
If j shetcnt Then Exit Sub
Sheets(j).Name = Cells(i, 1).Value
Next
End Sub


--
Gary''s Student - gsnu200829


"Flumoxed" wrote:

I am new to excel but have to set the following up:
I have a front sheet with student names on it. Then I have up to 44 sheets
behind this. I just want to be able to somehow get those names onto the
sheet tab at the bottom of the page in the same order as on the list then use
this as a template.
So at the top of the list I have say Fred Bloggs: I need him to have his
name on the tab at the bottom instead of it saying "sheet 2" and then second
on list as name on bottom of third sheet tab etc. The data on the sheets
that refer to sheet one has already been set up but I have to do this 24
times and I'm sure there must be a quicker way than typing it in by hand each
time. Is there? Thank you very much.

name each sheet from a list on sheet 1
 

You said you have more worksheets set up than you have student's names to
put on them. Assuming you will want to use this macro next year and the year
after, and further assuming you might have less students in one of the
subsequent years, the macro below names the worksheets, starting with the
2nd one, according to your list and then renames the subsequent worksheets
back to SheetX (where X is the appropriate sheet number) so you won't have
left over names on the tabs from previous years. Just change the data in the
3 Const statements to match your actual conditions...

Sub DistributeNames()
Dim X As Long
Dim LastRow As Long

Const RowWithFirstName As Long = 2
Const ColumnWithNames As String = "A"
Const FrontSheetname As String = "Sheet1"

With Worksheets(FrontSheetname)
LastRow = .Cells(.Rows.Count, ColumnWithNames).End(xlUp).Row
For X = 2 To Worksheets.Count
If X <= LastRow Then
Worksheets(X).Name = .Cells(X, ColumnWithNames).Value
Else
Worksheets(X).Name = "Sheet" & X
End If
Next
End With
End Sub

--
Rick (MVP - Excel)


"Flumoxed" wrote in message
...
I am new to excel but have to set the following up:
I have a front sheet with student names on it. Then I have up to 44
sheets
behind this. I just want to be able to somehow get those names onto the
sheet tab at the bottom of the page in the same order as on the list then
use
this as a template.
So at the top of the list I have say Fred Bloggs: I need him to have his
name on the tab at the bottom instead of it saying "sheet 2" and then
second
on list as name on bottom of third sheet tab etc. The data on the sheets
that refer to sheet one has already been set up but I have to do this 24
times and I'm sure there must be a quicker way than typing it in by hand
each
time. Is there? Thank you very much.

name each sheet from a list on sheet 1
 

Sub MakeSheets()
Worksheets("Sheet1").Activate
Howmany = WorksheetFunction.CountA(Range("A:A"))
sheetcount = Worksheets.Count
For j = 1 To Howmany
Worksheets.Add After:=Worksheets(sheetcount)
sheetcount = sheetcount + 1
Set wks = Worksheets(sheetcount)
wks.Name = Worksheets("Sheet1").Cells(j, 1).Value
Next j
End Sub


works for me
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Flumoxed" wrote in message
...
I am new to excel but have to set the following up:
I have a front sheet with student names on it. Then I have up to 44
sheets
behind this. I just want to be able to somehow get those names onto the
sheet tab at the bottom of the page in the same order as on the list then
use
this as a template.
So at the top of the list I have say Fred Bloggs: I need him to have his
name on the tab at the bottom instead of it saying "sheet 2" and then
second
on list as name on bottom of third sheet tab etc. The data on the sheets
that refer to sheet one has already been set up but I have to do this 24
times and I'm sure there must be a quicker way than typing it in by hand
each
time. Is there? Thank you very much.