Create tabs named after a group of cells?

[BM]

Is it possible to create multiple tabs each being named after a particular
cell.

For example:

a1 Apple
a2 Pear
a3 Grape
a4 Banana

Could I somehow select that group of cells and have tabs automatically
generated named, Apple, Pear, Grape, and Banana?

I have a rather large list and would like to automate the process if possible.

Thanks in advance!

 

[Gord Dibben]

This macro will do what you wish.

Sub Add_Sheets()
Dim rCell As Range
For Each rCell In ActiveSheet.Range("A1:A10")
'alternative.......... For Each rCell In Selection
With Worksheets.Add(after:=Worksheets(Worksheets.Count))
.Name = rCell.Value
End With
Next rCell
End Sub


Gord Dibben MS Excel MVP

 

[Paul B]

BM, you can with a macro,

Sub Name_Sheets()
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

And if you are new to macros you may also what to have a look here on
getting started with macros

http://www.mvps.org/dmcritchie/excel/getstarted.htm
--
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

 

[Dave F]

Just make sure none of the names in the range repeat each other; i.e., they
have to be unique names. Otherwise this code returns an error.

Dave