I have 71 worksheets, on sheet 1 in column B from B4 there are 70
names in cells, how do I get the name onto the tabs for the next 70
sheets. Plus on each sheet in D1 the name appear as well,
I may have to make 2 workbooks instead of having 71 worksheets in 1
workbook to make it easier. I have posted a similar request before but
I still have trouble with it. Could someone please help

This little macro assumes that the first sheet is named "directory", adjust
to suite:

Sub name_um()
Dim shnames(100) As String
Sheets("directory").Activate
For i = 0 To 69
shnames(i) = Cells(i + 4, "B").Value
Next
For i = 2 To 71
Worksheets(i).Name = shnames(i - 2)
Next
End Sub
--
Gary''s Student - gsnu2007c


"duckie" wrote:

I have 71 worksheets, on sheet 1 in column B from B4 there are 70
names in cells, how do I get the name onto the tabs for the next 70
sheets. Plus on each sheet in D1 the name appear as well,
I may have to make 2 workbooks instead of having 71 worksheets in 1
workbook to make it easier. I have posted a similar request before but
I still have trouble with it. Could someone please help

I'm not sure what you meant by Sheet 1. Here are two example that should
help. One case Sheet1 is the index to the sheet and the other is the string
name of the sheet. You need to have the code skip the first sheet.

For Each sht In ThisWorkbook.Sheets

If sht.Index < 1 Then
sht.Name = sht.Range("D1")
End If
Next sht
End Sub

For Each sht In ThisWorkbook.Sheets

If sht.Name < "Sheet1" Then
sht.Name = sht.Range("D1")
End If
Next sht
End Sub


"duckie" wrote:

I have 71 worksheets, on sheet 1 in column B from B4 there are 70
names in cells, how do I get the name onto the tabs for the next 70
sheets. Plus on each sheet in D1 the name appear as well,
I may have to make 2 workbooks instead of having 71 worksheets in 1
workbook to make it easier. I have posted a similar request before but
I still have trouble with it. Could someone please help

Hi,

This will help you get the sheet names in which ever cell you want.
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
But to get the names on the sheet i think you should type them by right
clicking on the sheet tab.
http://www.xldynamic.com/source/xld.xlFAQ0002.html
--
Thanks
Suleman Peerzade


"duckie" wrote:

I have 71 worksheets, on sheet 1 in column B from B4 there are 70
names in cells, how do I get the name onto the tabs for the next 70
sheets. Plus on each sheet in D1 the name appear as well,
I may have to make 2 workbooks instead of having 71 worksheets in 1
workbook to make it easier. I have posted a similar request before but
I still have trouble with it. Could someone please help