Try this construct by Dave Peterson.

Assumes the cells are in column A of Sheet1

Sub CreateNameSheets()

Dim ListWks As Worksheet
Dim ListRng As Range
Dim myCell As Range

Set ListWks = Worksheets("Sheet1")
With ListWks
Set ListRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
'or Set ListRng = Selection
End With

For Each myCell In ListRng.Cells
Worksheets.Add
On Error Resume Next
ActiveSheet.Name = myCell.Value
If Err.Number < 0 Then
MsgBox "Please fix: " & ActiveSheet.Name
Err.Clear
End If
On Error GoTo 0
Next myCell

End Sub


Gord Dibben MS Excel MVP

On Wed, 14 Nov 2007 12:23:02 -0800, Giggly4g
wrote:

I think I'm missing something. I copied and pasted the code but it keeps
hanging up after adding the first worksheet. If I change the value of inX to
4 or 172 or some other number, it creates a worksheet for just that cell (A4
or A172) but stops with the follow error...

Run-time error "9":
Subscript out of range

Please help...

"JonR" wrote:

Here's some code that will go down from A1 to A? (until it runs into an empty
cell) on the "Master" sheet and will create and name sheet according to what
is in each cell.

HTH

JonR

Sub NewSheet()

'This assumes the cells you wish to name the sheets after are in Column A
'on the "Master" worksheet.
'inX is a variable used to count down the rows.


Dim inX As Integer

Dim stName As String

inX = 1

Do Until Cells(inX, 1).Value = ""

stName = Cells(inX, 1).Value

Sheets.Add

ActiveSheet.Name = stName

Worksheets("Master").Activate

inX = inX + 1

Loop

End Sub



"Robert Maddox" wrote:

Is it possible to make multple worksheets from a selection of multiple cells?
This would mean a selection of 10 cells would generate 10 sheets titled with
the cell conent.