Hi. I have a list of names in a workbook (cells A1 through A20). I would
like to have individual worksheet tabs with these names. Is there any way
(other than copy and paste) to create worksheet tabs with these 20 names? VB
code would be acceptable here as well.

Mike, try this,

Sub name_sheets()
'will add a sheet, and name it
'for each name in column A
'from A1 down till it hits a blank row
Dim Rng As Range
Dim ListRng As Range
Set ListRng = Range(Range("A1"), Range("A1").End(xlDown))
For Each Rng In ListRng
If Rng.Text < "" Then
With Worksheets
.Add(after:=.Item(.Count)).Name = Rng.Text
End With
End If
Next Rng
End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Mike D." wrote in message
...
Hi. I have a list of names in a workbook (cells A1 through A20). I would
like to have individual worksheet tabs with these names. Is there any way
(other than copy and paste) to create worksheet tabs with these 20 names?
VB
code would be acceptable here as well.

Try

Sub sheetnames()
For Each sh In Worksheets
a = a + 1
sh.Name = Cells(a, "A").Value
Next sh

End Sub

"Mike D." wrote:

Hi. I have a list of names in a workbook (cells A1 through A20). I would
like to have individual worksheet tabs with these names. Is there any way
(other than copy and paste) to create worksheet tabs with these 20 names? VB
code would be acceptable here as well.

If you dont want to mess aound with VB, you van try this.
I just tested it and it works. It takes less than 10 seconds.

Say your sheet names are MySheet1, MySheet2, MySheet3 ...

Get your sheet names in a column.
Add an extra cell on the top and type in the word "Sheets" to that cell.
So now you have :

Sheets
MySheet1
MySheet2
MySheet3
.....

Now you make a Pivot Table.
The data source is the list of sheets.
In the Layout, put Sheets in the Page section and in the data section as a
Count.
Next, next, next, Done.
You should now have a Pivot table showing a total which should be the total
number of lines in your list of sheets.

Now you need to use the Pivot table Toolbar in order to access a magic
option.
Get your Pivot table Toolbar up in your Toolbars if it's not there already.
Right-click any empty space in the toolbar area and then activate the Pivot
table Toolbar.
It should show now.
Click somewhere in the Pivot table.

Then click on the Pivot table Toolbar and go to the Show Pages option.
It will ask you to choose from the available Pages.
As there is only "Sheets" available, choose "Sheets".

Excel will create the new sheets.

Select all the sheets using the Shift key and your mouse.
Select the cells containing the Pivot table in any sheet.
Delete the rows conating the Pivot table.
Unselect all the sheets.
Select just one.
Done.

Andy

Just to clarify.
The trick is to use Excel's capacity to create sheets from a Pivot table.
People are frightened of Pivot tables but they don't bite.

When Excel has finished creating the sheets, you just join all the sheets
together and zap the rows containing the PT. And then unjoin them. So you
have a new pile of clean sheets!

Andy

The more I think about it the more I realise :

This solution is fantastic

Andy

Andy:

Hi. I just got to trying this option. This works great and is very easy!

Thanks for your help,
Mike.

"Andy Morton" wrote:

The more I think about it the more I realise :

This solution is fantastic

Andy