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.

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.

How can this be modified to "look" at only a specific list of names to create
the sheets?

Thanks,
anna

"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.

The way this is written, it goes down Column A one cell at a time. Cell
references in VBA are Cells(row,column), and you can see in the do While loop
that the row is being incremented as each sheet is created.

If you have certain criteria that you want to meet, you could do it with If
statements or maybe a Select Case argument, depending on your needs. It
might help if you could send an example of what you're trying to do so I can
help meet your needs.
--
HTH

JonR


"anna" wrote:

How can this be modified to "look" at only a specific list of names to create
the sheets?

Thanks,
anna

"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.

Thanks. On my "master" sheet, I have a column labeled "People" in A$, with
the list starting in A5. Other users would input their data here so the list
could vary in length. I would like separate sheets generated with these names
so then the users could input results per "person" in their respective tabs.
This would include 3 columns... such as column one = "date" , column 2 =
"time", and column 3 = "result data". I think I figured out how to summarize
the data in a separate "summary" sheet by referring to the sheet name and
meeting multiple criteria (using this very helpful site), I just need this
last bit of info and I'm set! Of course if you can think of a better way to
do this, then please share. Thanks so much for you help!!
Anna

"JonR" wrote:

The way this is written, it goes down Column A one cell at a time. Cell
references in VBA are Cells(row,column), and you can see in the do While loop
that the row is being incremented as each sheet is created.

If you have certain criteria that you want to meet, you could do it with If
statements or maybe a Select Case argument, depending on your needs. It
might help if you could send an example of what you're trying to do so I can
help meet your needs.
--
HTH

JonR


"anna" wrote:

How can this be modified to "look" at only a specific list of names to create
the sheets?

Thanks,
anna

"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.

Replace the line inX=1 with inX=5. Then when the code gets to the statement
Do While Cells(inX,1)...., it will start at the cell on Row(inX) Column(1),
which is A5.

The way it's written, this code will run until it hits a blank in column A.
If you need to skip a blank, or want to stop, start, or skip certian cells,
you'll need to add some If statements.

--
HTH

JonR


"anna" wrote:

Thanks. On my "master" sheet, I have a column labeled "People" in A$, with
the list starting in A5. Other users would input their data here so the list
could vary in length. I would like separate sheets generated with these names
so then the users could input results per "person" in their respective tabs.
This would include 3 columns... such as column one = "date" , column 2 =
"time", and column 3 = "result data". I think I figured out how to summarize
the data in a separate "summary" sheet by referring to the sheet name and
meeting multiple criteria (using this very helpful site), I just need this
last bit of info and I'm set! Of course if you can think of a better way to
do this, then please share. Thanks so much for you help!!
Anna

"JonR" wrote:

The way this is written, it goes down Column A one cell at a time. Cell
references in VBA are Cells(row,column), and you can see in the do While loop
that the row is being incremented as each sheet is created.

If you have certain criteria that you want to meet, you could do it with If
statements or maybe a Select Case argument, depending on your needs. It
might help if you could send an example of what you're trying to do so I can
help meet your needs.
--
HTH

JonR


"anna" wrote:

How can this be modified to "look" at only a specific list of names to create
the sheets?

Thanks,
anna

"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.

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.

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.